Macro to create new workbook and copy data

Solved/Closed
sujith - Mar 15, 2010 at 09:24 AM
Rthmanden Posts 2 Registration date Tuesday September 6, 2016 Status Member Last seen September 13, 2016 - 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

14 responses

sujithjayan Posts 3 Registration date Monday March 15, 2010 Status Member Last seen March 16, 2010 10
Mar 16, 2010 at 08:22 AM
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
0
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
0