techEverest
asked on
Excel VBA to consolidate multiple workbooks into one
Hello Experts,
I have many excel workbooks(in a folder) and would like to combine into one single workbook. For example each workbooks have columns from A to CL and each workbooks have the same heading only with varying number of rows in each documents. I do not know vba and will try to learn at some point in time, I have been using codes from web to do some other minor automation with vba. I used the following code and tested on some test workbooks and did append two workbooks contents into one but when doing the same with 2 real documents with one containing almost 5k rows and other a few hundred, it didnt append properly. Can you please help. I want the code to select all the data in each worksheet and just merge all into 1 workbook.Thanks in advance.
---Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.Fi leSystemOb ject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\cha nge\to\exc el\files\p ath\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp). Row).Copy
ThisWorkbook.Worksheets(1) .Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp). Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
source:http://www.oaultimate.com/office/merge-multiple-excel-files-into-a-single-spreadsheet-ms-excel-2007.html
I have many excel workbooks(in a folder) and would like to combine into one single workbook. For example each workbooks have columns from A to CL and each workbooks have the same heading only with varying number of rows in each documents. I do not know vba and will try to learn at some point in time, I have been using codes from web to do some other minor automation with vba. I used the following code and tested on some test workbooks and did append two workbooks contents into one but when doing the same with 2 real documents with one containing almost 5k rows and other a few hundred, it didnt append properly. Can you please help. I want the code to select all the data in each worksheet and just merge all into 1 workbook.Thanks in advance.
---Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating
Set mergeObj = CreateObject("Scripting.Fi
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\cha
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).
ThisWorkbook.Worksheets(1)
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).
Application.CutCopyMode = False
bookList.Close
Next
End Sub
source:http://www.oaultimate.com/office/merge-multiple-excel-files-into-a-single-spreadsheet-ms-excel-2007.html
ASKER
Sounds good but I dont want to add in extra tools at my work computer. Thanks for the info , I ll keep this handy for my other personal projects.
i have some questions, as i may have not understood your question properly.
are you trying to combine multiple workbooks into single workbook and each workbook as a worksheet into this single workbook?
are you trying to combine multiple workbooks all into one single sheet?
are these workbooks that are needed to be combined have only one sheet or more?
are you trying to combine multiple workbooks into single workbook and each workbook as a worksheet into this single workbook?
are you trying to combine multiple workbooks all into one single sheet?
are these workbooks that are needed to be combined have only one sheet or more?
ASKER
I am trying to combine multiple workbooks into a single workbook in one single sheet(append contents of each documents on top of each other. Each workbook has 1 sheets each.Basically, I am trying to consolidate individual documents into one single master document, and view ,edit, compare etc.
For example
workbook1 has following data
Table Column
a apple
workbook2
Table Column
b orange
--Desired output
Table Column
a apple
b orange
--actual workbook has thousands of records in each workbooks.
Hope this helps.
For example
workbook1 has following data
Table Column
a apple
workbook2
Table Column
b orange
--Desired output
Table Column
a apple
b orange
--actual workbook has thousands of records in each workbooks.
Hope this helps.
Does your workbooks data have columns headers? Do u want to exclude the headers ? Or you simply want everything to be included ?
ASKER
Instead of manually copy pasting header at the end, Yes, each workbooks have column headers. So, ideally, the consolidated workbook should have column headers and then entire worksheet data copied into it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Professor JimJam and everyone. The above vba code perfectly did the job. Code comments were helpful to follow so that I could modify the range for columns.
You are welcome. Thx for the feedback.
Thanks to you!!! ProfessorJimJam! Wow...this just did the work for me. Great! It worked!. Thank God I came across this page, been surfing the internet forever. You a HERO!
you are welcome Shally Steazy.
it is good to see that users are searching for existing answers before posting a new question. i am very glad to hear that this post has helped you as well.
it is good to see that users are searching for existing answers before posting a new question. i am very glad to hear that this post has helped you as well.
Thank you ProfessorJimJam.
Please, I will like to save the consolidated workbook it in a specific folder with a specific name. I believe this can be automated as well. Sorry, I'm new to VBA and I will really appreciate your help and effort.
Thank you.
PS: I sent you a private message. Please, I'll appreciate your feedback.
Please, I will like to save the consolidated workbook it in a specific folder with a specific name. I believe this can be automated as well. Sorry, I'm new to VBA and I will really appreciate your help and effort.
Thank you.
PS: I sent you a private message. Please, I'll appreciate your feedback.
you are welcome Shally.
i replied to your pvt message.
please note that this post belongs to a question asked by someone else. if you want to modify the code as per your need. i suggest you open a question.
i replied to your pvt message.
please note that this post belongs to a question asked by someone else. if you want to modify the code as per your need. i suggest you open a question.
ProfessorJimJam, please follow link below for my question. I'm hoping you could help. Thank you sir!
https://www.experts-exchange.com/questions/28967197/Excel-VBA-to-save-consolidate-multiple-workbooks-into-a-specific-folder.html
https://www.experts-exchange.com/questions/28967197/Excel-VBA-to-save-consolidate-multiple-workbooks-into-a-specific-folder.html
Have you tried this free add-in?
http://www.rondebruin.nl/win/addins/rdbmerge.htm
Regards,