[RESOLVED] [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Dear All,
I am currently having 55 workbooks in a folder (ex. “C:\Consolidation”) and each workbook contains 4 worksheets.
Now I need to consolidate the following three sheets 1. Total Consolidation, 2. State level Consolidation and 3. District Level Consolidation from all 55 workbooks and save it in three different sheets in a new workbook in the same folder (i.e. C:\Consolidation). That new file should be names as “COUNTRY LEVEL CONSOLIDATION”.
So now macro should open one workbook at a time and copy three worksheets separately and paste them including the heading (heading should be copied only for the 1st workbook and second workbook onwards no need to copy the headings) into three worksheets in a new workbook named as “COUNTRLY LEVEL CONSOLIDATION”.
When it opens the second workbook, it should again copy three worksheets data and paste them below the data that are already copied from the previous workbook. (Ex: assume that previous workbook data copied till 500 rows in 1st sheet, 150 rows in second sheet and 90 rows in third sheet then, second workbook data should be pasted from row 501 in first sheet, 151 in second sheet and 91 in third sheet excluding headings. This is how it should copy all 55 workbooks data into three work sheets in one workbook. But the current challenge is in each work sheet, the number of rows to copy is different.
I have attached 5 sample workbooks for your reference.
Is it possible to develop a macro to do these tasks? Please help me with the macro code.
Thank you so much for your help in advance.
Amrutha
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
i guess to start with you need to have 3 arrays 1 for each sheet, then just copy the range of each sheet into each respective array, this will make sure all sheet 1 data is in 1 array, sheet2 data in another etc, once you have all the data, combine the arrays in order, then check if the sheet can hold all the data, then simply put the array into the range(same size as array)
getting the files you will most likely need the filesystem object and some sort of loop
have a try and if you get stuck post back
Yes!!!
Working from home is so much better than working in an office...
Nothing can beat the combined stress of getting your work done on time whilst
1. one toddler keeps pressing your AVR's power button
2. one baby keeps crying for milk
3. one child keeps running in and out of the house screaming and shouting
4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
5. working at 1 O'clock in the morning because nobody is awake at that time
6. being grossly underpaid for all your hard work
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
nobody is going to do it for you, your going to have to learn it.
try it in steps
1, first make a loop that finds all the files in the folder and gets the path
2, you need some code that takes that path and opens the file and stores the sheets objects
3, code that takes the objects and put the data into an array(s)
4,when thats all finished you need code outside the loop to finish and put all the data together into a new sheet
4
Yes!!!
Working from home is so much better than working in an office...
Nothing can beat the combined stress of getting your work done on time whilst
1. one toddler keeps pressing your AVR's power button
2. one baby keeps crying for milk
3. one child keeps running in and out of the house screaming and shouting
4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
5. working at 1 O'clock in the morning because nobody is awake at that time
6. being grossly underpaid for all your hard work
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
does it matter what order the workbook data is entered into the consolidated book?
the order, would be the same as the 55 workbooks are opened
if so some change would be required to sort the order of opening the workbooks
Is it possible to develop a macro to do these tasks?
entry level
Code:
Dim shts(2)
Dim rw(2) As Long
mypath = "C:\Consolidation\"
Set newb = Workbooks.Add
shts = Array("Total Consolidation", "State level Consolidation", "District Level Consolidation")
'assumes new Workbook will contain 3 sheets by default, if not some sheets will have to be added
newb.Sheets(1).Name = shts(0)
newb.Sheets(2).Name = shts(1)
newb.Sheets(3).Name = shts(2)
rw(0) = 1
rw(1) = 1
rw(2) = 1
fname = Dir(mypath & "*.xls")
Do While Len(fname) > 0
Set Wb = Workbooks.Open(mypath & fname)
For s = 0 To 3
rws = Wb.Sheets(shts(s)).UsedRange.Rows.Count
cols = Wb.Sheets(shts(s)).UsedRange.Columns.Count
newb.Sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).value = Wb.Sheets(s).UsedRange.value
rw(s) = rw(s) + rws
Next
Wb.Close False
fname = Dir
Loop
newb.SaveAs mypath & "COUNTRY LEVEL CONSOLIDATION.xls"
newb.Close ' or omit if you want left open
this is untested and may contain errors
nobody is going to do it for you, your going to have to learn it.
it is sunday and i am bored, but i hope you will at least study the code and make whatever modifications are necessary
Last edited by westconn1; Jan 11th, 2014 at 06:24 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Hi westconn1,
While running this macro, I am getting the following error message stating "Compile error: Can't assign to array" and highlighting "shts" word. Could you please help me to correct this error?
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
change to Dim shts
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Now I am getting Run-time error '9' script out of range and it is highlighting the following line "newb.Sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).value = Wb.Sheets(s).UsedRange.value"
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
the workbook opened does not have a worksheet that exactly matches the names i used in the line above, which i copied from your original post
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Also in "Total Consolidation" sheet, it is taking 30 columns, but I need to take only A to K (11 columns) and in "District Level Consolidation" sheet I need to copy only 'O to AA' (columns 13)
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Also in "Total Consolidation" sheet, it is taking 30 columns, but I need to take only A to K (11 columns) and in "District Level Consolidation" sheet I need to copy only 'O to AA' (columns 13)
i still do not see where you mention this previously
oops my mistake change to For s = 0 To 2
which columns for state level?
Last edited by westconn1; Jan 13th, 2014 at 05:59 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Sorry it was my mistake I didn't mention in my previous thread.
For "Total Consolidation" sheet it should copy from row A6 to K6 and till the last row where data exists
for "State level Consolidation" sheet it should copy from A1 to G1 and till the last row where data exists
for "District Level Consolidation" sheet it should copy from O1 to AA1 and till the last row where data exists
Also macro should copy the headings only for the first time and 2nd time onwards, it should skip headings and copy only data. Please help me westconn1.
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
as i can not easily test, i am not sure i have all the number of rows correct, you will have to test, check for blank or missing lines between workbook data, or if any rows get overwritten
i am reposting the entire code as it is too messy now to edit
Code:
Dim rw(2) As Long
mypath = "C:\Consolidation\"
shts = Array("Total Consolidation", "State level Consolidation", "District Level Consolidation")
Set newb = Workbooks.Add
'assumes new Workbook will contain 3 sheets by default, if not some sheets will have to be added
newb.Sheets(1).Name = shts(0)
newb.Sheets(2).Name = shts(1)
newb.Sheets(3).Name = shts(2)
rw(0) = 1
rw(1) = 1
rw(2) = 1
rng = Array("a6:k", "a2:g", "o2:aa")
cols = Array("11", "7", "13")
heds = Array("a1:k1", "a1:g1", "o1:aa1")
fname = Dir(mypath & "*.xls")
Do While Len(fname) > 0
Set Wb = Workbooks.Open(mypath & fname)
For s = 0 To 2
rws = Wb.Sheets(shts(s)).UsedRange.Rows.Count - 1
If s = 0 Then rws = rws - 6
If Not headsdone Then
newb.Sheets(shts(s)).Range("a1").Resize(, cols).value = Wb.Sheets(shts(s)).Range(heds(s)).value
headsdone = True
End If
newb.Sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).value = Wb.Sheets(s).Range(rng(s) & rws - 1).value
rw(s) = rw(s) + rws - 1
Next
fname = Dir
Loop
newb.SaveAs mypath & "COUNTRY LEVEL CONSOLIDATION.xls"
newb.Close
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Dear Sir,
I am getting the following error "Run-time error '1004' Application-defined or object defined error and highlighting the following line of code "newb.sheets(shts(s)).Range("a1").Resize(, cols).Value = wb.sheets(shts(s)).Range(heds(s)).Value"
When I skip the above line of code and move to the next one it is showing another error "Run-time error '9' script out of range" and highlighting the following line of code "newb.sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).Value = wb.sheets(s).Range(Rng(s) & rws - 1).Value" Please help me what to do.
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
But I have several other problems that are mentioned below
1) Column heading being copied only for 'Total Consolidation' sheet and for other two sheets heading is not being copied (Heading should be copied only for one time for these two sheets).
2) In sheet 'Total Consolidation' data is being copied from row 6, hence at the end for five rows and 11 columns (i.e. A:K) i am seeing #N/A values.
3) Also In sheets 'State level Consolidation' and 'District Level Consolidation' data is being copied from row 2, hence at the end for 'State level Consolidation' sheet 1 row and 7 columns (i.e. A:G) and 'District Level Consolidation' sheet 1 row 13 columns (i.e. A:M) I am seeing these #N/A values.
4) Also these #N/As are counting as rows, so when next sheet data will be copied below these #N/A values which means for every sheet we see #N/As in 6 rows. This is for first sheet. For 2nd & 3rd sheets, one #N/A row being added. Is there a way to avoid these #N/As?
Re: [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
I could able to find the mistake in the below lines. The correct code should be:
i realised i had made that error, during the night
i am not sure i have all the number of rows correct, you will have to test,
change to
rws = Wb.Sheets(shts(s)).UsedRange.Rows.Count - 2
try
change to
if s = 2 then headsdone = True
#N/As in 6 rows.
is all the rows of data copied, or some omitted?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
Just wanted to know how do I post my code in a seperate window like the way you posted in previous posts 8 & 17 ? Please let me know. So that in my future posts I will also use those options.
Re: [RESOLVED] [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook
use code tags
[/code] at end
[code] at start
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete