Hi people,
I will greatly need some kind help please.
Here's the scenario.
I have a field that says A01, A02, A03 etc and there are thousands of rows in random order, in one single worksheet.
How do I create the script such that I can generate multiple excel files based on each unique field? (Example all rows containing A01 will be generated in one file, A02 in another file)
Thank you.
Last edited by westbay; 08-07-2011 at 07:46 AM.
'SHEET1 TO MANY WORKBOOKS
Here's a macro for parsing rows of data from one sheet to many workbooks named for the same values.My macro names the workbooks for values in the column PLUS today's date, you can take a stab at removing the date part...or leave it in, it's a good technique.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello westbay. This is how I'd do it
Please Login or Register to view this content.
Last edited by gjlindn; 08-06-2011 at 02:00 AM.
-GregIf this is helpful, pls click Star icon in lower left corner
I just thought of something else. If you don't want to use a macro and you're not opposed to showing the new tabs in pivot table format (could probably very closely mirror what your existing table looks like), you could create a pivot table from your main data source and then use the pivot table function Show Report Filter Pages to split the data out. You would have to know your way around pivot tables in order to do this, though.
Your script worked beautifully. Big thumbs up. Thank you very much.Originally Posted by JBeaucaire
To gjlindn (Greg): I sincerely thank you too. My objective here is to obtain separate excel files from the master data, so had to use macro to speed up the process.
Ah, yes...separate files is actually a bit easier since there's no need to worry about sorting. This is my code modified for saving workbooks.
Please Login or Register to view this content.
I need an added function to the script.
What if I have a WB with 3 sheets and these sheets are linked to one another by formula.
Can I still parse them out into new WBs with all the 3 sheets there with formulas intact?
Master WB (Sheet 1, 2 and 3)
The parsing will depend on the column unique field criteria in Sheet 2. (Sort out A01s, A02s and A03s into individual WBs)
End result
Individual new WBs (Sheet 1, 2 and 3)
Sheet 2 will only contain one single unique field. (Example A02)
Sheet 1 and 3 are ported over with the formulas linked to sheet 2.
This has code added to fix worksheet references for transferred sheets. If this doesn't work for you, post an example of your workbook including the complete structure, but only some rows of data. Thanks!
Please Login or Register to view this content.
Thank you.
I still needed JBeaucaire's script functionality that I am able to select which exact column to split data and new WBs created according to the unique fields in the column.
Master WB - 3 sheets
Sheet 1 = Formulas dependent on Sheet 2
Sheet 2 = Contained A01, A02, A03 etc and I need JBeaucaire's script functionality as above.
Sheet 3 = Also containing A01, A02 and A03 but in a different column number. Will it be possible for JBeaucaire's script functionality to work on multiple sheets in the same WB?
Final Result
New WBs created according to the unique fields that I have selected in Sheet 2 specific column.
Sheet 1 = Sheet and all formulas copied over.
Sheet 2 = Sheet copied over and data containing A02 only.
Sheet 3 = Sheet copied over and data also containing A02 only.
Can a single VB script perform the above?
Give this a try, might need a little tweaking here or there...
Please Login or Register to view this content.
I have tried on a simple data report. It works well.
Now I have to get back office tomorrow to try on a more data intensive report.
Thanks a bunch again JBeaucaire!
The formula links in the new WBs, Sheet 1 are linking to the master WB.
I need them to link it to the new WBs, Sheet 2.
I realised I can only update the formula links if I re-saved the file into macro-enabled workbook.
a) Can the script saved these new files into xlsx or xlsxm?
I think this will do it. This macro names the new sheets in the new wb to match the names in the old workbook, then it does a REPLACE command on the cover page to remove the external reference from the formulas. In theory and in my test it worked.
Also, I've set the SAVEAS to save as xlsx, fileformat = 51. You can Google up some help on other formats, but xlsm would be 52 (be sure to edit the string name, too).
Please Login or Register to view this content.
Thank you for the above script. There's 2 things I want to correct.
1) My field data has the below:
ABC
ABC"blank"
It generated 2 files, ABC and ABC"Blank"
Can we modify the script to not generate file ABC"Blank" when there is a blank in the unique field identifer?
2) When the files are generated, I was prompted the error message below:
A formula or sheet you want to move or copy contains the name 'FunctionalAreas', which already exists on the destination worksheet. Do you want to use this version of the name?
To use the name as defined in the destination sheet, click Yes.
To rename the range refered to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box.
'Functional Area" is used as one of my column header. Even when I removed the words entirely, the script prompted the same message. Any idea what went wrong?
1) Best is to fix your data so there are no hidden spaces in the evaluation column. Use of drop downs and/or data validation when creating this data can fix that at the source, before the errors are introduced.
This "might" help, edit these two lines of code:
2) To eliminate the popup error message, add this line:Please Login or Register to view this content.
Please Login or Register to view this content.
I have amended the script to accomodate 5 working worksheets.
Tested it and there are 2 issues:
1) The newly created workbooks have ws2, 3, 4 & 5 named as "Sheet 2, 3, 4 & 5". I wanted ws2, 3, 4, 5 to retain the names as below.
Set ws2 = Sheets("Capital Projects")
Set ws3 = Sheets("Development Projects")
Set ws4 = Sheets("Capital Projects-CO")
Set ws5 = Sheets("Development Projects-CO")
2) There are individual modules like A01, A02, A03. However, A08 does not exist in ws2 and ws4. They are in ws3 and ws5. The newly created workbooks does not generate A08. It only has the rest like A01, A02, A03.
Please Login or Register to view this content.
You can take out all the lines of code referring the MyCount... that stuff isn't even being used anymore.
If you want the sheets to have a specific name, then name them.
Please Login or Register to view this content.
You are only using ws2 to collect all the values? That's not right, if you're going to do this you need all the values from all the sheets.
Having shown that, I think I'm done here. I wouldn't do any of this this way any longer, you're trying to take a specific function macro and frankenstein it into something it's not. The fact that you're splitting each sheet by a different column (are you?) is reason enough to abandon this altogether. All this data needs to be on one sheet for this work properly... in which case the original macro would work fine.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks