macro to create new workbook and copy dataSolved/Closed

Question

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

sujithjayan · Accepted Answer

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.

rizvisa1 · Answer

See this thread
https://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 · Answer

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.

sujithjayan · Answer

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.

dib · Answer

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.

RWomanizer · Answer

hi Dib,

Could you elobrate your problem and what exactly you required by pasting some sort of your data.

Thanks,
Rahul

fudzi · Answer

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!

Sakthi · Answer

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?

Raja · Answer

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!

ejwcpa · Answer

The macro shown here posted by rizvisa and as shown here:
https://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?

Syline · Answer

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 · Answer

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 · Answer

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 · Answer

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 ;)

Macro to create new workbook and copy data

14 responses

Similar discussions

Subscribe To Our Newsletter!