+ Reply to Thread
Results 1 to 6 of 6

Macro or VBA to consolidate several sheets in same workbook, different no of columms& rows

  1. #1
    Registered User
    Join Date
    11-23-2015
    Location
    Oxford, England
    MS-Off Ver
    2010
    Posts
    54

    Macro or VBA to consolidate several sheets in same workbook, different no of columms& rows

    Hi

    I've looked at other threads and tried out the solutions in those, but can't find anything that works for my needs. I have a template workbook with several sheets, four of which I need to merge, or consolidate, into one summary/master sheet, however there are several other sheets that won't need to be summarised. The sheets to be summarised contain largely the same columns, expect for a couple towards the end, but may contain any number of rows, and the header detail only needs to be pulled forward once, except where the columns are different of course. The sheets contain a lot of data validation, and I am not sure if this is causing the problem. This might seem like a simple copy and paste job, but this is a template and I will eventually have over 100 workbooks to perform this in and I need to try and automate it as much as possible. I need the values pulled forward rather than the formulae, as I have found that the formulae cause problems with the conversion of US$ to GB£.

    The data in the summary or master sheet will then be pulled forward into another workbook, summarising the position by sector.

    I attach a sample file to show you roughly what I mean, and hope that someone can help. If you need further explanation please let me know.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    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: Macro or VBA to consolidate several sheets in same workbook, different no of columms&

    Hi and welcome to the forum.

    First add a new Summary sheet. When I did this the VBA sheet code name became by default 'Sheet1' which suprised me so just check that's what you get. If not either change it's code name in the VBE or change the reference in the macro below to whatever the code name is.
    Add the header row to the summary sheet.
    Then run this code

    Please Login or Register  to view this content.
    Make sure the sheets that are in a different layout don;t have the text 'Cost Centre and Publisher' in A3. To include thse sheets in the macro we'll need to understand how the layout differs. i.e. same columns just different order, same columns but with additional columns, some columns missing. When we understand this then you'll need a pre-processing macro to put these into the standard layout so that they too can be included.
    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.

  3. #3
    Registered User
    Join Date
    11-23-2015
    Location
    Oxford, England
    MS-Off Ver
    2010
    Posts
    54

    Re: Macro or VBA to consolidate several sheets in same workbook, different no of columms&

    Hi Richard

    Thanks so much for this code, deceptively simple I think! It worked perfectly, I'm very grateful. I decided it might be easier to have all of the columns in all of the sheets that need to be summarised in the same order but hide the ones not applicable to each sheet. The code works really well on that, so I'm not sure I want to mess with it!!

    Thanks again, that's such a help, I spent the whole day yesterday trying to get this solution too. I'm absolutely thrilled!

  4. #4
    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: Macro or VBA to consolidate several sheets in same workbook, different no of columms&

    Hi,

    Glad to have helped and thanks for the rep.

    Yes hiding the columns that aren't common will be fine in that if the columns are also Hidden in the summary you won't SEE them, however since the CURRENTREGION property of the Range("A3") is used in the macro and assuming the hidden columns contain a contiguous series of values then whatever is in the hidden columns will have been copied too.

    If you want to avoid the data in the hidden columns being copied then change the line

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    where 'n' is the number of columns that should be included in the range to be copied

  5. #5
    Registered User
    Join Date
    11-23-2015
    Location
    Oxford, England
    MS-Off Ver
    2010
    Posts
    54

    Re: Macro or VBA to consolidate several sheets in same workbook, different no of columms&

    Hi

    I actually need all of the columns in the summary, so the original code worked just fine and didn't interfere with the hidden columns either. Perfect! (But I'll keep a note of your additional code for future reference - never know when you might need it, so it'll go in my virtual 'shed'! ).

    Thanks again

  6. #6
    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: Macro or VBA to consolidate several sheets in same workbook, different no of columms&

    Quote Originally Posted by 1953CAG View Post
    Hi

    But I'll keep a note of your additional code for future reference - never know when you might need it, so it'll go in my virtual 'shed'! ).
    Know what you mean and have many T-Shirts. I had to build a house extension to accommodate the overflow from my shed.

+ 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. consolidate various sheets in one workbook
    By jorgemels in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2014, 05:00 AM
  2. Macro to identify specific rows in many sheets and consolidate onto one sheet
    By Ospenc1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2013, 12:00 PM
  3. Help! Macro to consolidate data from multiple workbook sheets!
    By Sainath Krishnan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2012, 08:18 AM
  4. delete the rows and columms
    By Skeighter in forum Excel General
    Replies: 6
    Last Post: 11-09-2007, 01:49 PM
  5. [SOLVED] How do I covert rows to columms?
    By Mint in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2006, 05:15 PM
  6. Rows into Columms
    By Mint in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2006, 03:55 PM
  7. Consolidate workbook sheets to another workbook sheet
    By rudawg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2006, 04:25 AM

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