+ Reply to Thread
Results 1 to 16 of 16

Vba/macro to combine multiple workbooks with multiple sheets into one workbook

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Hi Guys

    I currently have multiple excel workbooks (11 workbooks), with multiple spreadsheets (approximately 12 sheets per). Each workbook is derived from a common template. I would like to combine these workbooks into a master document, i.e. one workbook with 12 sheets, with the corresponding information from each exel spreadsheet from each workbook, found on the coressponding sheet in the master document. Each document also has a prompt which asks to be updated when opening it. it is not necessary to update the document. Can anyone please assist me. Thanks.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Thanks arlu1201. I have tried to run the code and I get a :runtime error '9': subscript out of range". I did the debugging of it and it occurs after the line highlighted whe going through the debugging process as shown in this picturePic 1.png

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Oops sorry, below Dim i as long near the dim statements, add this as the last one

    Dim lastrow as long

  5. #5
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Thanks, i have checked the code, and in the code that you sent, it is included. I think the problem is from the line line "lrow = .Range("A" & .Rows.Count).End(xlUp).Row" onwards

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Actually i just checked and the Dim lastrow as long statement already exists.

    What error do you get now?

  7. #7
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    It is the same error that i have posted above

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Ok, the reason you are getting the error is - the sheet name in the master file does not match the sheet name in the individual files.

    Are they named differently?

  9. #9
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Cool thanks, i was using a test file and actually didn't rename the sheets in the master, so it works. But it is adding the data together. i need to have the data from sheet 1 workbook 2 pasted below the data from sheet 1 workbook 1, if that makes any sense. Can you help me with that. Sorry, I think I was a bit unclear

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Yes, the code is doing just that. Adding the data to the next available row of each sheet.

  11. #11
    Registered User
    Join Date
    07-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Okay, from my test file, there is four of names and 16 numbers on each sheet, two sheets per name. What it does is display one name from from the second book and sometimes all the names from book two. It seems to be by-passing book 1. All three are now exactly the same...also, if cells are locked, how can i get the code to work without unlocking them manually?

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    If the sheets are protected or the cells are protected / locked, it wont allow the macro to pick up those cells. So you will need to add the unprotect sheet command and password before copying each sheet and once the copying is done, protect it again.

  13. #13
    Registered User
    Join Date
    11-10-2013
    Location
    Hollywood, FL
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Hello. Thank you for this! Can we make this work if the worksheet names are not exactly the same? For example, I have some files with "comments" or "feedback" or other comments included in source file sheet names. Thanks again.

  14. #14
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Hi
    I have the same requirement,with little bit of changes
    My master book has 8 sheets of which 6 sheets needs to get the data from 3 source files, source files also have more than 10 sheets
    But the 6 sheets name is common in both master file and source files, i need to get the range (B13:PF1162) from all the sheets in to respective sheet in master file and later paste it as values
    Please assist!!Thanks

  15. #15
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Hi
    I have the same requirement,with little bit of changes
    My master book has 8 sheets of which 6 sheets needs to get the data from 3 source files, source files also have more than 10 sheets
    But the 6 sheets name is common in both master file and source files, i need to get the range (B13:PF1162) from all the sheets in to respective sheet in master file and later paste it as values
    Please assist!!Thanks

  16. #16
    Registered User
    Join Date
    09-03-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    1

    Re: Vba/macro to combine multiple workbooks with multiple sheets into one workbook

    Thank you so much!!! it is working just fine! You are the best!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1