none
How to merge data from multiple worksheets RRS feed

  • Question

  • I was given an excel file with what must be well north of a hundred worksheets. I do not know the names of each worksheet. All the worksheets have the same type of data organized into four columns. I need to have all the data from all worksheets to be on a single worksheet. With a lot of grunt work I could simply copy and paste the data from each worksheets into single worksheet. But this seems like a very unattractive solution for obvious reasons. Is there a fairly straightforward way of merging all of the data into a single worksheet?
    Thursday, July 31, 2014 4:35 AM

Answers

  • Yes.  Do it all the time.

    Sub CombineSheets()
    
    numsheets = Sheets.Count
    
    Set newSht = Sheets.Add(after:=Sheets(numsheets))
    newSht.Name = "Summary"
    
    Sheets(1).Cells.Copy Destination:=newSht.Cells
    For shtCount = 2 To numsheets
       destLastRow = newSht.Range("A" & Rows.Count).End(xlUp).Row
       With Sheets(shtCount)
          sourceLastRow = .Range("A" & Rows.Count).End(xlUp).Rows
    
          .Range("A2:D" & sourceLastRow).Copy _
             Destination:=newSht.Range("A" & (destLastRow + 1))
             
       End With
    
    Next shtCount
    
    
    End Sub
    


    jdweng

    • Marked as answer by Aaron Hartley Friday, August 1, 2014 4:11 PM
    Thursday, July 31, 2014 5:08 AM

All replies

  • Yes.  Do it all the time.

    Sub CombineSheets()
    
    numsheets = Sheets.Count
    
    Set newSht = Sheets.Add(after:=Sheets(numsheets))
    newSht.Name = "Summary"
    
    Sheets(1).Cells.Copy Destination:=newSht.Cells
    For shtCount = 2 To numsheets
       destLastRow = newSht.Range("A" & Rows.Count).End(xlUp).Row
       With Sheets(shtCount)
          sourceLastRow = .Range("A" & Rows.Count).End(xlUp).Rows
    
          .Range("A2:D" & sourceLastRow).Copy _
             Destination:=newSht.Range("A" & (destLastRow + 1))
             
       End With
    
    Next shtCount
    
    
    End Sub
    


    jdweng

    • Marked as answer by Aaron Hartley Friday, August 1, 2014 4:11 PM
    Thursday, July 31, 2014 5:08 AM
  • Yes.  Do it all the time.


    jdweng

    Hi Joel and thank you for responding. I tried the code and I got this error message after the first sheet:

    Run Time Error 1004: Application-defined or object-defined error

    A google search turns up mentions of periodically closing the worksheet or narrowing the range but in the context of code that is very different from your post. Do you have any suggestions for how to go about patching the 1004 issue?
    Thursday, July 31, 2014 4:13 PM
  • The code assumes all the worksheets have data.   You may be getting the error is you have a blank worksheet.   I also create a new sheet and cal it "Summary".  You will also get this error if the "Summary" sheet exists.  Also if you have excel 2003 you may get this error if the summary sheet get more than 65,536 rows. 

    Let me know which line is yellow the next time it fails.

    I assume the data is in columns A to D and I'm removing row 1 from every sheet except the first sheet.


    jdweng

    Thursday, July 31, 2014 4:40 PM
  • re:  code problem?

    Joel,
    Looks like the 2nd  ".Rows" should be .Row...
       sourceLastRow = .Range("A" & Rows.Count).End(xlUp).Row 's
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Edited by James Cone Monday, October 31, 2016 7:12 PM
    Thursday, July 31, 2014 5:03 PM
  • The code assumes all the worksheets have data.   You may be getting the error is you have a blank worksheet.   I also create a new sheet and cal it "Summary".  You will also get this error if the "Summary" sheet exists.  Also if you have excel 2003 you may get this error if the summary sheet get more than 65,536 rows. 

    Let me know which line is yellow the next time it fails.

    I assume the data is in columns A to D and I'm removing row 1 from every sheet except the first sheet.


    jdweng

    I haven't looked at all of the the worksheets but all of the worksheets that I have seen all have data.

    If the Summary worksheet exists I delete prior to running the macro. That is not the cause of the problem.

    I'm running Excel 2010.

    The quantity of records is probably somewhere is the 20 thousands.

    The correct columns are indeed A through D.

    There are no column names in the first line but this would not cause the 1004.

    The yellow line:

          .Range("A2:D" & sourceLastRow).Copy _
             Destination:=newSht.Range("A" & (destLastRow + 1))


    Thursday, July 31, 2014 8:14 PM
  • Hover over the following variables when it fails and let me know the values.  Also check the sheets that copies as indicated by shtCount to see if the 1st and last rows of each sheet match the summary sheet.

    1) shtCount

    2) sourceLastRow

    3) destLastRow.

    There may be garbage data at the end of one of the sheets.  Usually I click on the first empty row of each sheet then press Shift-CNTL-Down Arrow to highlight all the empty rows.  Then delete these rows.  I also do it for the columns.  Sometimes a worksheet gets corrupted.  If I still have issues I will add a new sheet and copy data from corrupted sheet to new sheet.  Then delete the corrupted sheet.


    jdweng

    Thursday, July 31, 2014 8:41 PM
  • Hover over the following variables when it fails and let me know the values.  Also check the sheets that copies as indicated by shtCount to see if the 1st and last rows of each sheet match the summary sheet.

    1) shtCount

    2) sourceLastRow

    3) destLastRow.


    jdweng

    shtCount 2

    sourceLastRow "MC"

    destLastRow 220

    Friday, August 1, 2014 3:42 PM
  • Take the 'S' off a Rows.

    sourceLastRow = .Range("A" & Rows.Count).End(xlUp).Rows


    jdweng

    Friday, August 1, 2014 3:45 PM
  • Take the 'S' off a Rows.

    sourceLastRow = .Range("A" & Rows.Count).End(xlUp).Rows


    jdweng


    Worked great. Thank you so much for your help.
    Friday, August 1, 2014 4:10 PM