+ Reply to Thread
Results 1 to 14 of 14

Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

  1. #1
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Hi.

    I have multiple workbooks with multiple sheets. The workbooks each have the same layout and sheet names. What's different would be the values in each tab. I have a master workbook, same number of sheets with sheet names, etc. which I would like to be updated with the *summation* of the values in the other workbooks, in other words a consolidated workbook. Most VBA's I've come across copy and paste and combine everything into one sheet, But I need to maintain the sheet names in the consolidated master file. In each sheet in each workbook the "items" are listed vertically in column C, and columns F & G carry the values. So I'm wondering if a VBA can be created to look up the "item" in column C and return a sum value for that particular item in each workbook to the masterfile in the respective sheet.

    Other things to bear in mind:
    - there is a particular tab that I do not want to sum but copy and paste each cell with data into the masterfile in the same sheet. Column headings are B to J in row 23.
    - there are formulas in the master file that sum certain rows on a particular sheet. Its either the macro replaces the formula and performs a sum itself based on the item in column C or it disregards cells with formulas altogether. I'm thinking the latter could save running time. NB: The cells that require updating all contain '0'.
    - If I want to exclude a particular sheet from each workbook can this also be included?

    Additional info:
    In each sheet the headings in columns F and G are on row 6, but the first "item" to look up is on row 10. There are headings in column C but there are sporadic.

    Appreciate any help I can get on this!

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    What you are requesting can be done. But we need to see a working sample file, as per the yellow banner above, to assist further. One question to answer up front is whether all the files will be in the same directory or not?

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Hi, and what's the reason for distributing data across several workbooks each with several sheets?

    Is this because each workbook belongs to a different person and completed only by them?
    Is 2016 your latest version of Excel or do you have 365 - sometimes users forget to update their details?

    Have you some principled reason for not wanting all data to be copied and pasted into a new single sheet database in your master workbook.
    That's generally the best way to analyse and summarise data.

    It needn't change the sheet names in the master file, although given a single sheet database could contain all the information you require, wherever you want to see it, it would make populating the individual sheets much easier, and arguably make them redundant if you were to switch on a Pivot Table.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Yes, each workbook belongs to a different person and represents a specific territory. There are financial packages. The final template is fixed and the individual sheets are necessary. We're not allowed to change the format, hence the reason why I want to update specific cells in the individual sheets. So basically, instead of referencing each individual workbook with a plus sign between and having to do that again each month or quarter I was hoping there would be a macro that can accomplish it.

    I do have 365.

    I understand what you're saying to a certain extent about the single sheet and then populating the individual sheets. I wouldn't mind trying that. Would that mean I would need a vlookup in the individual sheets referencing the master sheet.

    I'm not comfortable sharing the template here, is the description above sufficient? - where each sheet has categorical items in Column C, prior year values in column F and current year values in column G? This is consistent for each workbook. The prior and current year headings are on row 6. Column C row 6 is blank.

  5. #5
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Yes, all files will be in the same directory.

    The sample attached is what each sheet will look like except the final sheet - lets call it Debt facility. I hope this helps.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    I do have 365.
    Then please update your forum profile. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    ...I should have asked do all your users have 365?

    The reason I ask is that 365 has the new 'Collaboration' functionality which I believe is far far superior to the old abysmal 'Shared' workbook functionality. This would allow your users to use their own sheets in the Master workbook which would simplify the task of analysing / summarising data - with or without a pukka single sheet database.

  8. #8
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Yes, they all have 365.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    ....in that case I'd be inclined to check out the Collaboration functionality first before you make too many decisions.

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    We also need to see how you want the master laid out. please share a desensitized version of the master workbook.

  11. #11
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Hi Maniacb. I was able to combine a macro I found online with one that I used before and so far so good. I am however struggling to exclude a couple visible worksheets and any hidden worksheets. I've tried Select case, if statements, and xlsheetsvisible but no good. The macro below could probably use some refining as I've been trying different things. Its the line that shows : If sourceData.Name <> "Debt Facilities " And sourceData.Name <> "Heading" And sourceData.Name <> "DM2" Then - this seems to work with just one sheet being excluded but not multiple. Can you assist please?


    Please Login or Register  to view this content.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I have done it for you today.)

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Try changing the 'AND' to 'OR'

  14. #14
    Registered User
    Join Date
    02-15-2021
    Location
    Barbados
    MS-Off Ver
    Microsoft Office 365 ProPLus
    Posts
    8

    Re: Multiple workbooks with multiple sheets into a Masterfile with same number of Sheets

    Sorry that didn't work.


    I'm now running the macro on a different set of workbooks that were downloaded from Google drive from google sheets to xlsx. Why am I getting a type mismatch error on this line? In the watch window it says the "Expression not defined in context". It occurs half way down the 13th sheet in the master file. Quite baffling.


    Master.Worksheets(.Name).Cells(f.Row, "G") = Master.Worksheets(.Name).Cells(f.Row, "G") + sourceData.Range("G" & k)
    Last edited by jayped; 02-18-2021 at 12:09 PM. Reason: Difficulty sending original message.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] multiple sheets to masterfile (different cells)- VBA
    By vurkac03 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-17-2021, 06:18 AM
  2. Replies: 1
    Last Post: 06-28-2019, 02:32 AM
  3. macro on how to copy data in multiple workbooks into one masterfile
    By jengesmeriz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2018, 11:30 PM
  4. A Masterfile to summarize multiple workbooks
    By Hokkaido in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2016, 10:24 AM
  5. Replies: 1
    Last Post: 09-23-2014, 06:42 PM
  6. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  7. Generic Page Setup Macro applied to multiple workbooks with varying number of sheets
    By sowetoddid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2005, 11:45 AM

Tags for this Thread

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