[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
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.
Question by:FlyingElvii
  • 3
  • 2
LVL 19

Expert Comment

by:Richard Daneke
ID: 37043975
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.

Author Comment

ID: 37044336
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.

Author Comment

ID: 37046169
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 26

Accepted Solution

redmondb earned 2000 total points
ID: 37048309

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


Author Closing Comment

ID: 37062603
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!
LVL 26

Expert Comment

ID: 37062874
Many thanks, FlyingElvii!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question