Macro to create new workbook and copy data [Solved/Closed]

sujith - Mar 15, 2010 at 09:24 AM - Latest reply: Rthmanden 2 Posts Tuesday September 6, 2016Registration date September 13, 2016 Last seen
- Sep 12, 2016 at 09:53 AM
Hello

I am looking for macro to copy rows based on partial cell content of a column. I have an excel spreadsheet called "arc.xlsx" from which I would like to copy data to other few new excel files when certain criteria are met. The excel file contained location is C:\Documents and Settings\xxxx\Desktop\Company. Am only a beginner in Excel.

Below is a sample of arc.xlsx

GP BR CUST_NO CUST_NAME day mo year
I1 01 999999 SMITH 00 08 09
I1 ab 999999 SMITH 04 08 09
I1 cd 999999 SMITH 04 10 09
I1 01 999999 SMITH 04 01 10
I1 02 999999 SMITH 27 02 10
I1 01 999999 SMITH 27 02 10
I1 cd 999999 SMITH 02 03 10
I1 cd 999999 SMITH 04 03 10
I1 cd 999999 SMITH 30 07 09
I1 ab 999999 SMITH 30 07 09
I1 02 999999 SMITH 30 07 09

I would like the macro to copy rows that have 'ab' in the column B (with title BR)and save it in a new excel file with name ab.xlsx in the same location folder.
And athe same for 'cd', '01' and '02' by saving the data in files with name cd.xlsx, 01.xlsx so on.

Appreciate a lot for your help.
Thanx and regards,
Sujith
See more 

26 replies

Best answer
sujithjayan 3 Posts Monday March 15, 2010Registration date March 16, 2010 Last seen - Mar 16, 2010 at 08:22 AM
10
Thank you
Yep I did the same and an error window poped up saying "Run-time error '9': subscript out of range" and then when I hit Debug it highlights- Sheets("Sheet1").Select in the VB code page. There was a worksheet created with name tempsheet with no data in it.

Thank you, sujithjayan 10

Something to say? Add comment

CCM has helped 1664 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 16, 2010 at 09:46 AM
Your data needs to be on a sheet titled "Sheet1" or make a change in these lines


Sheets("Sheet1").Select

Where is says Sheet1, enter your data sheet name. So if my sheet is called Data 1, I would change it to

Sheets("Data 1").select

This lines appears more than once, so you have to make changes in all it occurances
This is a great code that I need as well! It seems to work, but I have a glitch half way through the creation of the workbooks. It stops and highlights the "ActiveWorkbook.SaveAs supName" line. Is it getting tripped up on the characters used in the column that its choosing the name from?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Dec 27, 2011 at 02:25 PM
@mikebeth1996 "s it getting tripped up on the characters used in the column that its choosing the name from?'
I would say most probably. The name needs to be the legal name by which a file can be saved
Hi, I need to write a macro in Excel 2010, that could creat a new workbook and copy the sheet from active workbook into the new workbook, please suggest me the code if any one used it before.Any help would be highly appreciated..!!!!
thanks
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 25, 2012 at 04:46 PM
@saad, you can use macro recorder to get the macro. All you have to do is start recorder, create a new book, copy the data and stop the macro
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 15, 2010 at 01:12 PM
3
Thank you
See this thread
http://ccm.net/forum/affich-258700-interesting-question-for-excel-champions

It does do pretty much same except it creates new sheet, instead of new work book. You can modify it and it can create new book if that what you want
sujithjayan 3 Posts Monday March 15, 2010Registration date March 16, 2010 Last seen - Mar 15, 2010 at 03:34 PM
0
Thank you
Hi Riz,
Am sorry but I didnt get a clue after having a look at the post link. as mentioned am just a beginner in excel.
Hope I would get a better solution.
Thanx anyway.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 15, 2010 at 04:49 PM
STEPS

1. MAKE A BACKUP OF YOUR WORKBOOK

2. Open the work book

3. Press ALT + F11 (both ALT key and F11 key at the same time). This open VBE

4. From the menu of VBE, click on Insert and then choose on Module by clicking on it. This will open a blank module

5. Copy the code give after the instructions by selecting the code (will be found after the instructions) and pressing CTRL + C (both keys at the same time)

6. Paste the code in the newly added module (see step 4) by clicking on the module and pressing CTRL + V (again both at the same time)

7. Make sure there is no red line in the pasted code.

8. Press F5 to run the macro.

9 Check the documents in the default location where generally excel saves the file.

HERE IS THE CODE

Sub details()

Dim thisWB  As String

Dim newWB As String

    thisWB = ActiveWorkbook.Name
    
    On Error Resume Next
    Sheets("tempsheet").Delete
    On Error GoTo 0
    
    Sheets.Add
    ActiveSheet.Name = "tempsheet"
    
    Sheets("Sheet1").Select
    
    If ActiveSheet.AutoFilterMode Then
        Cells.Select
        
        On Error Resume Next
        
        ActiveSheet.ShowAllData
        
        On Error GoTo 0
    
    End If
    
    Columns("B:B").Select
    Selection.Copy
    
    Sheets("tempsheet").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    If (Cells(1, 1) = "") Then
        lastrow = Cells(1, 1).End(xlDown).Row
        
        If lastrow <> Rows.Count Then
            Range("A1:A" & lastrow - 1).Select
            Selection.Delete Shift:=xlUp
        End If
    
    End If
    
    Columns("A:A").Select
    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
                CopyToRange:=Range("B1"), Unique:=True
    
    Columns("A:A").Delete
    
    Cells.Select
    Selection.Sort _
            Key1:=Range("A2"), Order1:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    lMaxSupp = Cells(Rows.Count, 1).End(xlUp).Row
    
    For suppno = 2 To lMaxSupp
    
        Windows(thisWB).Activate

        supName = Sheets("tempsheet").Range("A" & suppno)
        
        If supName <> "" Then

            Workbooks.Add
            ActiveWorkbook.SaveAs supName
            newWB = ActiveWorkbook.Name
            
            Windows(thisWB).Activate

            Sheets("Sheet1").Select
            Cells.Select
            
            If ActiveSheet.AutoFilterMode = False Then
                Selection.AutoFilter
            End If
            
            Selection.AutoFilter Field:=2, Criteria1:="=" & supName, _
                        Operator:=xlAnd, Criteria2:="<>"
            
            lastrow = Cells(Rows.Count, 2).End(xlUp).Row

            Rows("1:" & lastrow).Copy
            
            Windows(newWB).Activate
            ActiveSheet.Paste

            ActiveWorkbook.Save
            ActiveWorkbook.Close
            
        End If
    
    Next
    
    Sheets("tempsheet").Delete
    
    Sheets("Sheet1").Select
    If ActiveSheet.AutoFilterMode Then
        Cells.Select
        ActiveSheet.ShowAllData
    End If

End Sub



Hey the code is very nice...but could you please help me for coping the data from one workbook to another, formats are specified.I am trying this.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jan 2, 2012 at 11:53 AM
@Snehal : ".but could you please help me for coping the data from one workbook to another, formats are specified.I am trying this."

What you mean ?
Thanks rizvisa1
sujithjayan 3 Posts Monday March 15, 2010Registration date March 16, 2010 Last seen - Mar 15, 2010 at 06:38 PM
0
Thank you
Hi Riz,
Than a lot for your help.

I tried to run it but had no clue what was happening. Now I partially have some code that gives me the exact solution. Actually I used a macro to create an other macro. Can you help me modify it?

With the code below I could extract the similar values from column b and paste it in a new excel sheet with filename branch01.xlsx on my desktop.

Excel file - abc.xlsx is

GP BR CUST_NO CUST_NAME day mo year
I1 01 999999 SMITH 00 0M AR
I1 ab 999999 SMITH 04 08 09
I1 cd 999999 SMITH 04 10 09
I1 01 999999 SMITH 04 01 10
I1 02 999999 SMITH 27 02 10
I1 01 999999 SMITH 27 02 10
I1 cd 999999 SMITH 02 03 10
I1 cd 999999 SMITH 04 03 10
I1 cd 999999 SMITH 30 07 09
I1 ab 999999 SMITH 30 07 09
I1 02 999999 SMITH 30 07 09

What I need is a macro to create separate excel files for values in column b. ( like branch01.xlsx for value '01', branch02.xlsx for '02', branchab.xlsx for value 'ab' and branchcd.xlsx for value 'cd' )


Sub SelectRowsG()
Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("b"))
If c = "01" Then
If rngG Is Nothing Then Set rngG = c.EntireRow
Set rngG = Union(rngG, c.EntireRow)
End If
Next c
rngG.Select
End Sub
Sub branch01()
'
' branch01 Macro
'

'
Application.Run "abc.xlsx!SelectRowsG"
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Suji\Desktop\branch01.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub


Appriciate your help.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 15, 2010 at 06:59 PM
I took your data that you earlier gave


GP BR CUST_NO CUST_NAME day mo year
I1 01 999999 SMITH 00 08 09
I1 ab 999999 SMITH 04 08 09
I1 cd 999999 SMITH 04 10 09
I1 01 999999 SMITH 04 01 10
I1 02 999999 SMITH 27 02 10
I1 01 999999 SMITH 27 02 10
I1 cd 999999 SMITH 02 03 10
I1 cd 999999 SMITH 04 03 10
I1 cd 999999 SMITH 30 07 09
I1 ab 999999 SMITH 30 07 09
I1 02 999999 SMITH 30 07 09


I ran the macro that I posted above

I ended up with cd.xlsx (with 4 rows) ab.xlsx (with 2 rows), 2.xlsx (with 2 rows) and 1.xlsx (with 3 rows)

Did you follow the instructions ? Including where to look for the file ?
0
Thank you
Hi Riz,

thanks for your information on how to copy the data.
i've succeded after following your instructions.
I've this problem of doing the same task for other column
as the code above only for column B
can you tell me how can I do the same task to column D, AJ, AK...
Thank you
Waiting for your response.
Hi, did you get the answer ? If you did, could you please send me an email to sylinelee at gmail.com please ? thanks
RWomanizer 368 Posts Monday February 7, 2011Registration dateContributorStatus September 30, 2013 Last seen - May 12, 2011 at 07:10 AM
0
Thank you
hi Dib,

Could you elobrate your problem and what exactly you required by pasting some sort of your data.

Thanks,
Rahul
0
Thank you
Hi,
I have the same issue as Dib.

If I would for instance have to make the same action but based on "day" (column "E" instead of "B") in given example above, can I use the same code just by adjusting it in some places? Which ones?

Great Thanks!
0
Thank you
Hi,

In excel I have to create template as if I put all details of purchasing order with each field have validation for restricted rules, and after entered data the file has to be split the values into 3 or 4 *.csv file format for integrating with backend process. Let me know your idea?
0
Thank you
Hi, Can any one help me to copy the excel sheet data from one sheet to another sheet?

-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)

Sn Code Type next calib
5 BPR CORR 7-Apr-12
4 BPR CORR 7-Apr-12
73 BPR CORR 7-Apr-12
9 BRG CORR 8-Apr-12
10 BRG CORR 8-Apr-12
11 BRG CORR 8-Apr-12
17 BRG CORR 9-Apr-12
311 DP CORR 9-Apr-12
227 DP CORR 9-Apr-12
227 DP CORI R 9-Apr-12

Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.

Example: Today's Date is 8-Apr-12
So, I want to copy following record to new work sheet (when I click the button / run the macro).

Sn Code Type next calib
9 BRG CORR 8-Apr-12
10 BRG CORR 8-Apr-12
11 BRG CORR 8-Apr-12

Please help to write the code.
Thanks!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 8, 2012 at 08:37 AM
@Raja
You can get the macro by using Macro recorder and recording your action
ejwcpa 1 Posts Wednesday January 15, 2014Registration date January 15, 2014 Last seen - Jan 15, 2014 at 04:51 PM
0
Thank you
The macro shown here posted by rizvisa and as shown here:
http://ccm.net/faq/7053-excel-macro-to-create-new-workbook-and-copy-data
creates new workbooks and copies data based upon the data in column B so that you end up with new excel files such as ab.xls, cd.xls, etc.

What if I want to copy data and create new workbooks based on the data in column E so that I end up with new workbooks and copied data based upon teh data in column E and end up with excel files such as 4.xls, 20.xls, 30.xls, etc.

What areas in the code need to be changed?
0
Thank you
Hi,
I also need help because I have to use the column A instead of B.
What I have to change ? Could someone help me please ?
screwtape 1 Posts Friday April 24, 2015Registration date April 24, 2015 Last seen - Apr 24, 2015 at 08:56 AM
0
Thank you
In my case this runs for a really long time - my data is huge - it seems to be creating workbook based on ID except I dont see where these newly created workbooks are being saved.

In fact when I re run the script it actually prompts to replace already created workbooks - where is this script dumping the new files - I didnt change anything I copied the script as is
kombarris 1 Posts Monday May 23, 2016Registration date May 23, 2016 Last seen - May 23, 2016 at 03:44 AM
0
Thank you
Thanks for this. It works great! One question though, can someone point out the line of code I need to adjust if I want to the first 3 rows of the spreadsheet to be copied as header rows................Thanks
Rthmanden 2 Posts Tuesday September 6, 2016Registration date September 13, 2016 Last seen - Sep 12, 2016 at 09:53 AM
0
Thank you
I have a challenging addition to this question:

is it possible to create a userform (pop up window) in Excel that allows you to select more than one criteria to extract into the same Excel file. for example, in above case it was a matter of extracting those cases containing 'ab' and pasting it into a new workbook with the title 'ab.xls' but what if i wanted the records containing 'ab' and 'cd' or 'cd' and '02'. is it possible to have a selection box (like the normal dropdown filter in a list) so you can select which criteria should be met (multiple) and then extract it?

Thanks in advance!

good quest ;)