g48dd
Board Regular
- Joined
- Jan 12, 2009
- Messages
- 101
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:
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?
Code:
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