Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
I have over 50 workbooks in a folder called Report. They all have
the same layout. Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. Alltotals has all the headings and associated graphs. I would also then like that file saved as AlltotalsMonthYear . The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. The data I would like to sum is B6:S6 to B60:S60. I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
try this code. the codew tests each filename in report and make sure it
doesn't open the All file twice. It puts the File name in column a in the total book and thne Sum in column b. then it creates a Grand total at the end of the all total Book. Sub totalbooks() Folder = "C:\Report" AllFileName = "Alltotals" LenAll = Len(AllFileName) 'Open All total book Set Allbk = Workbooks.Open(Filename:=Folder & "\" _ & AllFileName & ".xls") Set AllSht = Allbk.Sheets(2) 'find last row of in column A LastRow = AllSht.Range("A" & Rows.Count).End(xlUp).Row 'Newrow is row where report total is put NewRow = LastRow FName = Folder & "\*.xls" Do While FName < "" 'Don't open allmonth files If Left(UCase(FName), LenAll) < UCase(Alltotals) Then Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) Set Report_T_Sht = Reportbk.Sheets("Total") Set TotalRange = Report_T_Sht.Range("B6:S60") Total = WorksheetFunction.Sum(TotalRange) NewRow = NewRow + 1 AllSht.Range("A" & NewRow) = FName AllSht.Range("B" & NewRow) = Total Reportbk.Close End If FName = Dir() Loop 'add total to All total book as a formula AllSht.Range("A" & (NewRow + 2)) = "GRAND TOTL" AllSht.Range("B" & (NewRow + 2)).Formula = _ "=SUM(B" & (LastRow + 1) & ":B" & NewRow & ")" bkMonth = AllSht.Range("R1") bkYear = AllSht.Range("S1") Allbk.SaveAs Filename:=Folder & "\" & AllFileName & bkMonth & bkYear Allbk.Close SaveAs:=False End Sub " wrote: I have over 50 workbooks in a folder called Report. They all have the same layout. Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. Alltotals has all the headings and associated graphs. I would also then like that file saved as AlltotalsMonthYear . The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. The data I would like to sum is B6:S6 to B60:S60. I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
On May 30, 8:44*pm, Joel wrote:
try this code. *the codew tests each filename in report and make sure it doesn't open the All file twice. *It puts the File name in column a in the total book and thne Sum in column b. *then it creates a Grand total at the end of the all total Book. Sub totalbooks() Folder = "C:\Report" AllFileName = "Alltotals" LenAll = Len(AllFileName) 'Open All total book Set Allbk = Workbooks.Open(Filename:=Folder & "\" _ * *& AllFileName & ".xls") Set AllSht = Allbk.Sheets(2) 'find last row of in column A LastRow = AllSht.Range("A" & Rows.Count).End(xlUp).Row 'Newrow is row where report total is put NewRow = LastRow FName = Folder & "\*.xls" Do While FName < "" * *'Don't open allmonth files * *If Left(UCase(FName), LenAll) < UCase(Alltotals) Then * * * Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) * * * Set Report_T_Sht = Reportbk.Sheets("Total") * * * Set TotalRange = Report_T_Sht.Range("B6:S60") * * * Total = WorksheetFunction.Sum(TotalRange) * * * NewRow = NewRow + 1 * * * AllSht.Range("A" & NewRow) = FName * * * AllSht.Range("B" & NewRow) = Total * * * Reportbk.Close * *End If * *FName = Dir() Loop 'add total to All total book as a formula AllSht.Range("A" & (NewRow + 2)) = "GRAND TOTL" AllSht.Range("B" & (NewRow + 2)).Formula = _ * *"=SUM(B" & (LastRow + 1) & ":B" & NewRow & ")" bkMonth = AllSht.Range("R1") bkYear = AllSht.Range("S1") Allbk.SaveAs Filename:=Folder & "\" & AllFileName & bkMonth & bkYear Allbk.Close SaveAs:=False End Sub " wrote: I have over 50 workbooks in a folder called Report. *They all have the same layout. *Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . *I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. *Alltotals has all the headings and associated graphs. *I would *also then like that file saved as AlltotalsMonthYear . *The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. *The data I would like to sum is B6:S6 to B60:S60. *I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob- Hide quoted text - - Show quoted text - Thanks Joel, The Alltotals workbook opens OK, but on the line Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) I get a run time error 1004, C:\Report\C:Report*.xls could not be found. Check the spelling of the filename, and verify that the file location is correct. Any ideas? Thanks Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
Dumb mistake
from: FName = Folder & "\*.xls" to: FName = Dir(Folder & "\*.xls") " wrote: On May 30, 8:44 pm, Joel wrote: try this code. the codew tests each filename in report and make sure it doesn't open the All file twice. It puts the File name in column a in the total book and thne Sum in column b. then it creates a Grand total at the end of the all total Book. Sub totalbooks() Folder = "C:\Report" AllFileName = "Alltotals" LenAll = Len(AllFileName) 'Open All total book Set Allbk = Workbooks.Open(Filename:=Folder & "\" _ & AllFileName & ".xls") Set AllSht = Allbk.Sheets(2) 'find last row of in column A LastRow = AllSht.Range("A" & Rows.Count).End(xlUp).Row 'Newrow is row where report total is put NewRow = LastRow FName = Folder & "\*.xls" Do While FName < "" 'Don't open allmonth files If Left(UCase(FName), LenAll) < UCase(Alltotals) Then Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) Set Report_T_Sht = Reportbk.Sheets("Total") Set TotalRange = Report_T_Sht.Range("B6:S60") Total = WorksheetFunction.Sum(TotalRange) NewRow = NewRow + 1 AllSht.Range("A" & NewRow) = FName AllSht.Range("B" & NewRow) = Total Reportbk.Close End If FName = Dir() Loop 'add total to All total book as a formula AllSht.Range("A" & (NewRow + 2)) = "GRAND TOTL" AllSht.Range("B" & (NewRow + 2)).Formula = _ "=SUM(B" & (LastRow + 1) & ":B" & NewRow & ")" bkMonth = AllSht.Range("R1") bkYear = AllSht.Range("S1") Allbk.SaveAs Filename:=Folder & "\" & AllFileName & bkMonth & bkYear Allbk.Close SaveAs:=False End Sub " wrote: I have over 50 workbooks in a folder called Report. They all have the same layout. Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. Alltotals has all the headings and associated graphs. I would also then like that file saved as AlltotalsMonthYear . The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. The data I would like to sum is B6:S6 to B60:S60. I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob- Hide quoted text - - Show quoted text - Thanks Joel, The Alltotals workbook opens OK, but on the line Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) I get a run time error 1004, C:\Report\C:Report*.xls could not be found. Check the spelling of the filename, and verify that the file location is correct. Any ideas? Thanks Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
One other small change
from If Left(UCase(FName), LenAll) < UCase(Alltotals) Then to If Left(UCase(FName), LenAll) < UCase(AllFileName) Then "Joel" wrote: Dumb mistake from: FName = Folder & "\*.xls" to: FName = Dir(Folder & "\*.xls") " wrote: On May 30, 8:44 pm, Joel wrote: try this code. the codew tests each filename in report and make sure it doesn't open the All file twice. It puts the File name in column a in the total book and thne Sum in column b. then it creates a Grand total at the end of the all total Book. Sub totalbooks() Folder = "C:\Report" AllFileName = "Alltotals" LenAll = Len(AllFileName) 'Open All total book Set Allbk = Workbooks.Open(Filename:=Folder & "\" _ & AllFileName & ".xls") Set AllSht = Allbk.Sheets(2) 'find last row of in column A LastRow = AllSht.Range("A" & Rows.Count).End(xlUp).Row 'Newrow is row where report total is put NewRow = LastRow FName = Folder & "\*.xls" Do While FName < "" 'Don't open allmonth files If Left(UCase(FName), LenAll) < UCase(Alltotals) Then Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) Set Report_T_Sht = Reportbk.Sheets("Total") Set TotalRange = Report_T_Sht.Range("B6:S60") Total = WorksheetFunction.Sum(TotalRange) NewRow = NewRow + 1 AllSht.Range("A" & NewRow) = FName AllSht.Range("B" & NewRow) = Total Reportbk.Close End If FName = Dir() Loop 'add total to All total book as a formula AllSht.Range("A" & (NewRow + 2)) = "GRAND TOTL" AllSht.Range("B" & (NewRow + 2)).Formula = _ "=SUM(B" & (LastRow + 1) & ":B" & NewRow & ")" bkMonth = AllSht.Range("R1") bkYear = AllSht.Range("S1") Allbk.SaveAs Filename:=Folder & "\" & AllFileName & bkMonth & bkYear Allbk.Close SaveAs:=False End Sub " wrote: I have over 50 workbooks in a folder called Report. They all have the same layout. Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. Alltotals has all the headings and associated graphs. I would also then like that file saved as AlltotalsMonthYear . The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. The data I would like to sum is B6:S6 to B60:S60. I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob- Hide quoted text - - Show quoted text - Thanks Joel, The Alltotals workbook opens OK, but on the line Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) I get a run time error 1004, C:\Report\C:Report*.xls could not be found. Check the spelling of the filename, and verify that the file location is correct. Any ideas? Thanks Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate (sum) last sheet (32) of all workbooks in a folder
On May 30, 10:38*pm, Joel wrote:
One other small change from If Left(UCase(FName), LenAll) < UCase(Alltotals) Then to If Left(UCase(FName), LenAll) < UCase(AllFileName) Then "Joel" wrote: Dumb mistake from: FName = Folder & "\*.xls" to: FName = Dir(Folder & "\*.xls") " wrote: On May 30, 8:44 pm, Joel wrote: try this code. *the codew tests each filename in report and make sure it doesn't open the All file twice. *It puts the File name in column a in the total book and thne Sum in column b. *then it creates a Grand total at the end of the all total Book. Sub totalbooks() Folder = "C:\Report" AllFileName = "Alltotals" LenAll = Len(AllFileName) 'Open All total book Set Allbk = Workbooks.Open(Filename:=Folder & "\" _ * *& AllFileName & ".xls") Set AllSht = Allbk.Sheets(2) 'find last row of in column A LastRow = AllSht.Range("A" & Rows.Count).End(xlUp).Row 'Newrow is row where report total is put NewRow = LastRow FName = Folder & "\*.xls" Do While FName < "" * *'Don't open allmonth files * *If Left(UCase(FName), LenAll) < UCase(Alltotals) Then * * * Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) * * * Set Report_T_Sht = Reportbk.Sheets("Total") * * * Set TotalRange = Report_T_Sht.Range("B6:S60") * * * Total = WorksheetFunction.Sum(TotalRange) * * * NewRow = NewRow + 1 * * * AllSht.Range("A" & NewRow) = FName * * * AllSht.Range("B" & NewRow) = Total * * * Reportbk.Close * *End If * *FName = Dir() Loop 'add total to All total book as a formula AllSht.Range("A" & (NewRow + 2)) = "GRAND TOTL" AllSht.Range("B" & (NewRow + 2)).Formula = _ * *"=SUM(B" & (LastRow + 1) & ":B" & NewRow & ")" bkMonth = AllSht.Range("R1") bkYear = AllSht.Range("S1") Allbk.SaveAs Filename:=Folder & "\" & AllFileName & bkMonth & bkYear Allbk.Close SaveAs:=False End Sub " wrote: I have over 50 workbooks in a folder called Report. *They all have the same layout. *Each workbook has 32 sheets (1-31 and the 32nd sheet at the end is called Total) . *I need to automatically open each sheet in the folder, go to each Total sheet and sum them in the 2nd sheet of a file called Alltotals. *Alltotals has all the headings and associated graphs. *I would *also then like that file saved as AlltotalsMonthYear . *The Month is in R1 and the year is in S1 on the Total sheet. I have headings A5:S5 and A5:A61. *The data I would like to sum is B6:S6 to B60:S60. *I am not sure whether using the consolidate and sum function is best or if there is another way. Thank you for any help. Bob- Hide quoted text - - Show quoted text - Thanks Joel, The Alltotals workbook opens OK, but on the line *Set Reportbk = Workbooks.Open(Filename:=Folder & "\" & FName) I *get a run time error 1004, C:\Report\C:Report*.xls could not be found. *Check the spelling of the filename, and verify that the file location is correct. Any ideas? Thanks Bob- Hide quoted text - - Show quoted text - Thanks again Joel, I made those changes and it worked. After seeing the result, I realised that I was unclear in my request. I essentialy wanted to consolidate (using sum) all the individual cells in the total sheets. I need to sum the individual cells in each total sheet (eg. b6 in 1st workbook + b6 in the 2nd + b6 in the 3rd ...+ b6 in the 50th aworkbook and return the total in cell b6 in the alltotals workbook. same for every other cell in the range. I hope this makes sense. Regards Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - need to consolidate workbooks | Excel Worksheet Functions | |||
Can I consolidate one work sheet from several workbooks? A few oth | Excel Discussion (Misc queries) | |||
Can I consolidate one work sheet from several workbooks? A few oth | Excel Discussion (Misc queries) | |||
Consolidate Several Workbooks into One | Excel Worksheet Functions | |||
consolidate 2 different workbooks | Excel Discussion (Misc queries) |