+ Reply to Thread
Results 1 to 13 of 13

Combine Data from Multiple Sheets into one Sheet

  1. #1
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Combine Data from Multiple Sheets into one Sheet

    I have a workbook with a sheet for each month of the year. I want to combine column A from the all the monthly Sheets but not the other three sheets (Event Totals, Monthly Totals, and Vendors 2017) into one sheet on the Event Totals sheet. I have included my code below. I am currently getting an error on If sh.Name <> "Event Totals" Or "Monthly Totals" Or "Vendors 2017" Then as well as Last = LastRow(sumSht). I would also like to add some code to the CopyRng to only copy the cells in column A from the monthly sheets which have data.

    Please Login or Register  to view this content.
    Last edited by billykiller05; 12-30-2016 at 05:15 PM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    If sh.Name <> "Event Totals" Or sh.Name <> "Monthly Totals" Or sh.Name <> "Vendors 2017" Then

    I suspect this code is copied from Ron's site.
    Last = LastRow(sumSht)
    is a separate function which is not in your code.
    The code does not know what is LastRow.
    Last edited by AB33; 12-30-2016 at 12:20 PM.

  3. #3
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    Thank you If sh.Name <> "Event Totals" Or sh.Name <> "Monthly Totals" Or sh.Name <> "Vendors 2017" Then fixed that error.

    If I change Dim i back to Dim Long will Last = LastRow(sumSht) work properly?

    Also is there anything I can add to CopyRng to only select cells in Column A with data to be copied?

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    Untested.
    If this does not work, please attach a sample.

    Please Login or Register  to view this content.
    Last edited by AB33; 12-30-2016 at 01:16 PM.

  5. #5
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    I used your code and it does copy and it only copies cells with data. But there is one problem and one addition I need.

    Problem - it copies all the monthly sheets and then Vendors 2017 skips a space and then copies all the monthly sheets again along with Vendors 2017 and Monthly Totals.

    I need to start copying at Row 2 since all my sheets have headers.

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    Please attach a sample. With out seeing the data, we are going around the circle.

  7. #7
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    Here is my file with some of the months removed and some sample data. Thank You.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    I have amended the code, but you need to have a header in the events sheet.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    AB33 Thank You so much for all your help.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    There is a minor error.
    Please change this line

    If CopyRng.Rows.Count > 2 Then

    INTO

    If CopyRng.Rows.Count > 1 Then

  11. #11
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    AB33 I made that change but I notice after I did that it now includes the Header from March and April and if I only have one row of data on these sheets that data is not included.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Combine Data from Multiple Sheets into one Sheet

    Sorry!
    Try this line instead

    If sh.Range("A" & Rows.Count).End(xlUp).Row > 1 Then

  13. #13
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Re: Combine Data from Multiple Sheets into one Sheet

    AB33 that was it. You are a genius.

+ 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. Combine data in multiple sheets to one sheet
    By RDevi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2016, 10:44 AM
  2. Replies: 0
    Last Post: 10-12-2015, 10:02 AM
  3. Combine columns data from multiple sheets to existing master sheet via cell value
    By 253.Asmo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2015, 12:13 PM
  4. Combine multiple sheets into one sheet
    By cherryab888 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2015, 05:57 PM
  5. [SOLVED] Formula to combine data from multiple sheets to a summary sheet
    By laurann in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2013, 02:38 PM
  6. Replies: 7
    Last Post: 10-19-2011, 08:33 AM
  7. Combine Data from Multiple Sheets to Single Sheet
    By mpquin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2009, 06:06 PM

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