run one macro after another... NEXT ?

[g48dd]

Hi I am using 2003, I am using the consolidate tool to consolidate data on 31 different sheets in the same work book. The consolidate tool will only take 11 sheets at a time, so I have to do 1-11, 12-21, 22-31 and the run consolidate again but this time I am consolidating the results of the first three to get 1 table. Here is the first macro:

Option Explicit

Sub Con_Month()
'
' Con_Month Macro
' Macro recorded 10/12/2010 by Ken Logan
'

'
    Sheets("Consolidation").Select
    Range("B6").Select
    Selection.Consolidate Sources:=Array( _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]1'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]2'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]3'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]4'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]5'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]6'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]7'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]8'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]9'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]10'!R10C1:R250C5" _
        , _
        "'C:\Documents and Settings\Ken Logan\Desktop\2010 Water Consolidation\[001.xls]11'!R10C1:R250C5" _
        ), Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
    
End Sub

Right after [001.xls] is the tab number or worksheet number, each represents one day.... the next code looks exactly the same save for the sheets are 12-21 and so on. If I wanted to combine all these macros or have one run another would I use next or is there a better way? This is all for one month and I have to do 12 months, so instead of naming the months Jan 2010 I just changed everything to 001, 002, 003. I don't need the month names I only need total for one year, and consolidate works great but it is limited. I don't mind making a bunch of macros if that is what I have to do; they are simple but I wondered if it is possible to combine or is it better to keep it short as making a long long long macr could make it difficult to make it work. What is the best idea?

 

[Ten98]

Difficult to know what the best solution is for you without seeing the data you're working on.

It may be that using consolidate is ineffecient compared with say, using a formula to add all the different tabs together?

Either way, I think you should record a macro for all 4 stages of what you're trying to do, not just the first stage.

 

[g48dd]

Thank you, I never got back, I have been working on this. What I did was recorded macro for each stage then called the next macro, so it is four separate macro's but runs as one.

Thanks
Ken