VBA Consolidation

[Bruce]

I'm trying to write a macro that will consolidate all the worksheets.

The only way I have found is to list the actual worksheet names in the consolidation array line. The files I want to use this macro for are automatically created and the names may change so I can't hard code them in.

 

[daleyman]

OK, I've got it.

Array("Sheet1!R11C1:R27C4", "Sheet2!R11C1:R27C4", "Sheet3!R11C1:R27C4")

...is not the same as the string you are compiling. The array function is not accepting the string with commas as compiled in the loop.

Have you tried a method similar to my first reply, where you just do a copy and insert-paste actually in the loop? This is tried and tested method by me many times.

Apologies for my earlier stupidity, am a bit slow tonight!

 

[Bruce]

Wouldn't that method just copy/paste and not sum up similiar items?

I haven't got to see my starting point yet, but it sounds like the initial spreadsheet will have around 90 workssheets.

I've written a macro that does a vlookup using cell A3's and a table with "A Group", "B Group" etc. to determine if it now goes to workbook 1, 2, 3, or 4. Then it copies the tab to the corrsponding workbook.

This last part would consolidate the worksheets in the individual new workbooks. I was hoping not to hard code in the sheet names as they may change on occasion.

Your help has been very appreciated!!
This message was edited by Bruce on 2002-04-19 15:19

 

[daleyman]

could you do the consolidate within the loop, consolidating one sheet at a time so that no array is required?