Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
Hi,
My workbook consists of 10 sheets. The names of the sheets a Total, Jan03, Feb03, Mar03, Apr03, May03, Jun03, Jul03, Aug03 and List. The workbook will expand with a new sheet each new month (Oct03, Nov03, Des03, Jan04....) The sheet named Total is used to consolidate the range B2:C65536 in all sheets, but not the sheet named List. By today my code look like: Sub Makro1() ' Sheets("Total").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'C:\Documents and Settings\[Bok2.xls]Apr03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Aug03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Feb03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jan03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jul03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jun03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Mar03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]May03'!R2C2:R65536C3") _ , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub I don't know much about Array functions, but isn't there an easy way to make a code that will consolidate all sheets in the workbook except those two named Total and List? Any help would be much appreciated. Regards, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
why not a dummy sheet after the last inserted sheet and
=sum(sheet1:last!b1) where b1 has the total for b2:c65536 "Paul" wrote in message om... Hi, My workbook consists of 10 sheets. The names of the sheets a Total, Jan03, Feb03, Mar03, Apr03, May03, Jun03, Jul03, Aug03 and List. The workbook will expand with a new sheet each new month (Oct03, Nov03, Des03, Jan04....) The sheet named Total is used to consolidate the range B2:C65536 in all sheets, but not the sheet named List. By today my code look like: Sub Makro1() ' Sheets("Total").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'C:\Documents and Settings\[Bok2.xls]Apr03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Aug03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Feb03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jan03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jul03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jun03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Mar03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]May03'!R2C2:R65536C3") _ , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub I don't know much about Array functions, but isn't there an easy way to make a code that will consolidate all sheets in the workbook except those two named Total and List? Any help would be much appreciated. Regards, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
Try this:
Sub Totals() Dim i%, SheetArg$() ReDim SheetArg(2 To Worksheets.Count-1) For i = 2 To Worksheets.Count-1 SheetArg(i) = Worksheets(i).Name & "!R2C2:R65536C3" Next i Sheets("Total").Select Range("B2").Select Worksheets("Sheet1").Range("A1").Consolidate _ Sources:=Array(SheetArg), _ Function:=xlSum End Sub "Paul" wrote in message om... Hi, My workbook consists of 10 sheets. The names of the sheets a Total, Jan03, Feb03, Mar03, Apr03, May03, Jun03, Jul03, Aug03 and List. The workbook will expand with a new sheet each new month (Oct03, Nov03, Des03, Jan04....) The sheet named Total is used to consolidate the range B2:C65536 in all sheets, but not the sheet named List. By today my code look like: Sub Makro1() ' Sheets("Total").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'C:\Documents and Settings\[Bok2.xls]Apr03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Aug03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Feb03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jan03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jul03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Jun03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]Mar03'!R2C2:R65536C3", _ "'C:\Documents and Settings\[Bok2.xls]May03'!R2C2:R65536C3") _ , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub I don't know much about Array functions, but isn't there an easy way to make a code that will consolidate all sheets in the workbook except those two named Total and List? Any help would be much appreciated. Regards, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
Thank you, for giving me the opportunity to learn about the consolidate
function. It is really powerful! "Paul" wrote in message ... Thx a lot :) I did some corrections, and my final code look like: Sub Totals() Dim i%, SheetArg$() ReDim SheetArg(2 To Worksheets.Count - 1) For i = 2 To Worksheets.Count - 1 SheetArg(i) = Worksheets(i).Name & "!R2C2:R65536C3" Next i Worksheets("Total").Range("B2").Consolidate _ Sources:=Array(SheetArg), _ Function:=xlSum End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
Hi again.
I've a follow-up question, and would appreciate if some of you could help me with this as well. Per today 20 workbooks are located in d:\timelist\data. All workbooks have one sheet named Total. I've also a workbook named Summary.xls that is located in d:\timelist. Is it possible to make a macro in Summary.xls (and the sheet named SumTotal) that can consolidate all sheets (range B2:C65536) named Total in all workbooks located in d:\timelist\data? I know that I can specify each work book in a macro like: Sub Makro1() Sheets("SumTotal").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'D:\timelist\data\[Bok1.xls]Total'!R2C2:R65536C3", _ "'D:\timelist\data\[Bok2.xls]Total'!R2C2:R65536C3", _ ..... and so on ..... , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub But, since I could get a new file next month located in d:\data\timelist, or even delete one, this is not appropriate. Regards, P |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
While in that folder, something like
for each wb in workbooks if wb.name <"Summary.xls" then next wb "Paul" wrote in message om... Hi again. I've a follow-up question, and would appreciate if some of you could help me with this as well. Per today 20 workbooks are located in d:\timelist\data. All workbooks have one sheet named Total. I've also a workbook named Summary.xls that is located in d:\timelist. Is it possible to make a macro in Summary.xls (and the sheet named SumTotal) that can consolidate all sheets (range B2:C65536) named Total in all workbooks located in d:\timelist\data? I know that I can specify each work book in a macro like: Sub Makro1() Sheets("SumTotal").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'D:\timelist\data\[Bok1.xls]Total'!R2C2:R65536C3", _ "'D:\timelist\data\[Bok2.xls]Total'!R2C2:R65536C3", _ ..... and so on ..... , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub But, since I could get a new file next month located in d:\data\timelist, or even delete one, this is not appropriate. Regards, P |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate Sources
Sub Totals()
Const MAXBOOK As Long = 20 Dim i%, SheetArg$() Dim sPath1 As String ReDim SheetArg(1 To MAXBOOK) Dim sPath As String, sFile As String ThisWorkbook.Worksheets("SumTotal") _ .Cells.ClearContents sPath = "D:\Timelist\Data\" i = 0 sPath1 = "D:\TimeList\Data\*.xls" sFile = Dir(sPath1) Do While sFile < "" i = i + 1 SheetArg(i) = "'" & sPath & _ "[" & sFile & "]Total'!R1C2:R16384C3" sFile = Dir() Loop ' For i = 1 To MAXBOOK ' Debug.Print i, SheetArg(i) ' Next ThisWorkbook.Sheets("SumTotal"). _ Range("A1").Consolidate _ Sources:=Array(SheetArg), _ Function:=xlSum, _ TopRow:=False, _ LeftColumn:=False, _ CreateLinks:=False End Sub this only does to row 16384 It doesn't seem to want to work using closed workbooks and down to 65536. I suspect there might be a problem with the number of external links. Think you need to be more conservative in the number of rows you want to attack. change MAXBOOK to equal the number of books in the directory. I only tested it with 5 workbooks. Remarks, Tom Ogilvy Paul wrote in message om... Hi again. I've a follow-up question, and would appreciate if some of you could help me with this as well. Per today 20 workbooks are located in d:\timelist\data. All workbooks have one sheet named Total. I've also a workbook named Summary.xls that is located in d:\timelist. Is it possible to make a macro in Summary.xls (and the sheet named SumTotal) that can consolidate all sheets (range B2:C65536) named Total in all workbooks located in d:\timelist\data? I know that I can specify each work book in a macro like: Sub Makro1() Sheets("SumTotal").Select Range("B2").Select Selection.Consolidate Sources:=Array( _ "'D:\timelist\data\[Bok1.xls]Total'!R2C2:R65536C3", _ "'D:\timelist\data\[Bok2.xls]Total'!R2C2:R65536C3", _ ..... and so on ..... , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False End Sub But, since I could get a new file next month located in d:\data\timelist, or even delete one, this is not appropriate. Regards, P |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PLEASE HELP! Return a Value Using Multiple Sources | Excel Discussion (Misc queries) | |||
Return a Value Using Multiple Sources | Excel Discussion (Misc queries) | |||
Editing sources | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets from Different Sources | Excel Worksheet Functions | |||
Pivottables - Can i consolidate 2 Data sources in Excel 2007? | Excel Discussion (Misc queries) |