+ Reply to Thread
Results 1 to 7 of 7

Consolidate all data from number of sheets into another sheet within a workbook

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Consolidate all data from number of sheets into another sheet within a workbook

    Hi,

    I have an excel file that has 5 sheets named
    "001"
    "002"
    "003"
    "004"
    "ALL"

    All the sheets have exactly the same headers with different data.

    I want to write a vba code such that the data from 4 sheets (001,002,003,004) will be copied into the sheet named "All" such that in sheet "All" I will have the same headers with a consolidated data from 001,002,003 and 005.

    Note that a sheet might not have a data and header at all, but there will definteley be at least one sheet containing the data above.

    See attached sample file.


    Is this possible?



    Thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    Hello Lilit2222,


    All the sheets have exactly the same headers...........
    Note that a sheet might not have a data and header at all
    Isn't this contradictory?

    For proper use and manipulation of data sets, Excel requires uniformity. Please ensure that all sheets at least have headings, including the "ALL" sheet then try the following code assigned to a button:-

    Please Login or Register  to view this content.
    The code assumes that you are consolidating all data from all worksheets into the "ALL" worksheet.

    I hope that this helps.

    Cheerio,
    vcoolio.

  3. #3
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    Thank you. I have made amendment such that all sheets including "ALL" have the same headers.

    However, when I try to run the code it brings "run time error 424 Object required" and the line starting with ws.UsedRange is highlighted.

    Do you have any suggestions?

    Many thanks,

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    Hello Lilit2222,

    The code works perfectly in your sample.
    Have you changed something in the code?
    Did you type the code into a module or did you copy and paste it from post #2?

    Cheerio,
    vcoolio.

    P.S. Is Sheet5 the "ALL" worksheet? Please note that Sheet5 is the sheet code.
    Last edited by vcoolio; 07-21-2020 at 07:40 AM. Reason: Add P.S.

  5. #5
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    How can I check whether the sheet code of "ALL" is Sheet5?

  6. #6
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    Thank you this has been resolved

  7. #7
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Consolidate all data from number of sheets into another sheet within a workbook

    Hello Lilit2222,

    Could you please post back and let us know how it was resolved. This will finalise this thread and will benefit anyone else who may come by this thread looking for a similar solution.

    Thank you.

    Cheerio,
    vcoolio.

+ 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 data from all sheets into one sheet
    By Remphan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2016, 09:01 PM
  2. [SOLVED] Select sheets in workbook then consolidate data
    By Manish84 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2014, 06:46 PM
  3. [SOLVED] Consolidate data from two sheets of same workbook
    By indianhp in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2013, 01:53 AM
  4. Replies: 20
    Last Post: 10-19-2012, 04:35 PM
  5. 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
  6. Replies: 1
    Last Post: 04-11-2012, 03:10 AM
  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

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