Consolidating Workbooks
Posted by Katie on December 08, 2001 7:24 AM
Here is my scenario: I have 3 workbooks with 15 sheets each. I want to take one sheet at a time (without hardcoding their names), one from each workbook to create 15 workbooks with 3 sheets each.
The macro I wish to write would do the following:
1. open the three workbooks (I know how to write this)
2. format each sheet in all workbooks the exact same way and delete hidden columns. (I have the code to format & delete columns, I just need to know where to place it in all of this)
3. move first sheet from workbook1 to new workbook & rename as MTH
4. move first sheet from workbook2 to behind the MTH sheet in workbook from step #3& rename as QTD
5. move first sheet from workbook3 to behind the QTD sheet in workbook in steps 3&4 & rename as YTD
6. prompt for path and filename to save as (since each time through the loop, the path & file name will be different.
7. Close new workbook
8. Next
NOTES: All three sheets to be moved are named the same thing in each original workbook so I can't move all three at one time and must rename the sheet before moving the next.
I know this is a lot. I'll take it in piecemeal if you've got it. Thanks :-) Katie
Posted by Jack on December 08, 2001 3:52 PM
Katie
Break your questions into chuinks or lumps and build it slowley, else i can see a mess your end.
If you can program VBA then build it up as needs be a post for help, but 8 questions al variable on data we have never seen, will not get you much help, i do simular so ill look at the first question and work from there, but to slove this lot will take weeks and many emails or posts,
i never cut data from sheets COPY and alway make a new SS that way ive lost nothing so to answer your question 1 create a new folder called x 6 dec 2001 report and sheets 1 and 2 and 3 .... 15
now dunp data copy into each as needs be...
thats answered q1 from there you should be routing the copy paste as needs be.
Post for more when set up so far.
HTH
