• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

How do I merge the data from multiple worksheets into a single worksheet?

I have a spreadsheet which contains multiple worksheets, 2 of which contain data of an identical structure. Each of the 2 worksheets contain ZIP CODE, STATE, CITY, and a series of 3 business related indicators (FLAG A, B, and C).

Worksheet A contains static data for the approximate 40,000 zip codes that are outside of our business zone.

Worksheet B contains the same data for the approximate 4,000 zip codes that are within our business zone. The cells for this sheet are dynamically linked to a separate spreadsheet which serves as a sort of central data repository.

I can't store all 44,000 rows in the data repository, as the update of a 1/4 million cells causes the update to fail via resources.

I need to sort and merge the data from worksheets A and B into worksheet C, so that I have a single data source for performing array functions (specifically a VLookup) from a census table, which can contain ZIPs from either of the 2 worksheets.
  • 3
  • 2
1 Solution
Richard DanekeTrainerCommented:
You can try to create, in a blank workbook, a pivot table that uses both worksheets from multiple ranges.  Use State for a row source and count of Zip for values to place some data into the workbook.   Double click on the grand total and Excel will output a new worksheet with all of the records.  

Your limits in 2002 are 65535 for rows and, in 2007 or 2010, over 1,048,000.  Trial versions are availabe directly from the Microsoft web pages.  But, there are many reasons not to upgrade.
FlyingElviiAuthor Commented:
Hi. Thanks for the response.

This looks like it's on the right track, but I need the syntax for including the 2 different worksheet ranges as my input for the wizard's 'step 2' (input for the pivot table).

My ranges are: 'All ZipsNonBusiness'!$A$1:$G$39509
                and: 'All ZipsBusiness'!$A$1:$G$4741

Thanks for your help.
FlyingElviiAuthor Commented:
Hi again.

I figured out how to use the multiple input sources, however I receive the following error:

"A field in your source data has more unique items than can be used in a PivotTable report. MicroSoft Excel may not be able to create the report, or may create the report without the data from this field."

No report was created.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.


If you're still interested in your original approach, please see the code below. A few notes...
(1) Set the XHEADER_ROWS constant to the no. of rows in the header (so it isn't duplicated from the second sheet). You can set this to 0 if you want all rows from both sheets.
(2) The macro will handle more than two sheets, just insert them into the line starting "For Each xOldSheet".
(3) The macro unprotects, unfilters, unhides rows and columns the two sheets.
(4) Because formulas may cause problems in the new "Merge" sheet, the macro pastes values only. Obviously it can be changed if you want formatting also or formulas instead.
(5) The macro doesn't bother checking if "Merge" already exists - instead it fails ungracefully.
(6) The results are slightly untidy if the second sheet only has the header rows (i.e. no data).
Option Explicit


Sub Merge_Two_Sheets_Paste_Values()

Dim xNewSheet As Worksheet
Dim xOldSheet As Worksheet
Dim xSheet As Worksheet
Dim xResponse As Long
Dim xhold As String
Dim xLast_Row As Long
Dim xStart_Row As Long
Dim xFirst As Boolean

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlManual
End With

    Set xNewSheet = Sheets.Add
    xNewSheet.Name = "Merge"
    xFirst = True
    For Each xOldSheet In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))
        If xOldSheet.ProtectContents Then xOldSheet.Unprotect ' If there's a password thrn add it as a string here.
        If xOldSheet.FilterMode Then xOldSheet.ShowAllData
        xOldSheet.Cells.EntireColumn.Hidden = False
        xOldSheet.Cells.EntireRow.Hidden = False
        xLast_Row = Sheets("Merge").Range("A1").SpecialCells(xlLastCell).ROW
        If xFirst Then
            xStart_Row = 1
            xFirst = False
            '''If xLast_Row = XHEADER_ROWS Then Exit For
            xStart_Row = XHEADER_ROWS + 1
            xLast_Row = xLast_Row + 1
        End If
        xOldSheet.Range("A" & xStart_Row & ":" & xOldSheet.Range("A1").SpecialCells(xlLastCell).Address).Copy
        Sheets("Merge").Cells(xLast_Row, 1).PasteSpecial (xlPasteValues)
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With

End Sub

Open in new window

FlyingElviiAuthor Commented:
This solution resolved my issue. Additionally, the code is re-usable for addressing any similar updates. It was very well-explained and easy to follow and understand.
Thanks Brian!
Many thanks, FlyingElvii!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now