combine data from 50 states into one sheet


New Member
Mar 7, 2008
I have 50 spreadsheets for US states, with cities,zipcodes,population,and other data.

Every column header for each spreadsheet is the same across all the fields/ cells.

I would like to combine all these spreadsheets into one, do all the editing and spreadsheet work on all the data at the same time. I have enough ram memory to support the spreadsheet size.

Each city has 20+ data fields associated with it.

Is there an easy way to combine all these sheets? I started working with each spreadsheet for each state, but it is very tedious having to repeat all the same work on each spreadsheet, additionally loading & saving each file,etc.

I'd like to just combine all the sheets into one big sheet do all the calculations,etc. and save the sheet for any additional changes. Then I can just sort the large sheet and separate the respective sheets based on my sort and search criteria.

I recall doing this kind of thing by copy paste in the past, but I thought there maybe a better way.

So... I'd appreciate any suggestions


Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Well-known Member
Apr 24, 2008
Try this on a copy of your data
Sub ConsolidateSheets()
'Author:    Jerry Beaucaire
'Date:      6/26/2009
'Updated:   6/23/2010
'Merge all sheets in a workbook into one summary sheet (stacked)
'Data is sorted by a specific column name
Dim cs As Worksheet, ws As Worksheet
Dim LR As Long, NR As Long, sCol As Long
Dim sName As Boolean, SortStr As String
Application.ScreenUpdating = False

'From the headers in data sheets, enter the column title to sort by when finished
SortStr = "Invoice #"

'Add consolidation sheet if needed
If Not Evaluate("ISREF(Consolidate!A1)") Then _
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Consolidate"

'Option to add sheet names to consolidation report
sName = MsgBox("Add sheet names to consolidation report?", vbYesNo + vbQuestion) = vbYes

Set cs = ActiveWorkbook.Sheets("Consolidate")
NR = 1

'Process each data sheet
    For Each ws In Worksheets
        If ws.Name <> "Consolidate" Then
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            'customize this section to copy what you need
            If NR = 1 Then      'copy titles and data to start the consolidation
                ws.Range("A1", ws.Cells(1, Columns.Count).End(xlToLeft)).Copy
                If sName Then
                    cs.Range("B1").PasteSpecial xlPasteAll
                    cs.Range("A1").PasteSpecial xlPasteAll
                End If
                NR = 2
            End If
            ws.Range("A2:BB" & LR).Copy     'copy data

            If sName Then       'paste and add sheet names if required
                cs.Range("B" & NR).PasteSpecial xlPasteValues
                cs.Range("A" & NR, cs.Range("B" & cs.Rows.Count).End(xlUp).Offset(0, -1)) = ws.Name
                cs.Range("A" & NR).PasteSpecial xlPasteValues
            End If
            NR = cs.Range("A" & cs.Rows.Count).End(xlUp).Row + 1
        End If
    Next ws

    LR = cs.Range("A" & cs.Rows.Count).End(xlUp).Row
    On Error Resume Next
    sCol = cs.Cells.Find(SortStr, After:=cs.Range("A1"), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    cs.Range("A1:BB" & LR).Sort Key1:=cs.Cells(2, sCol + (IIf(sName, 1, 0))), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    If sName Then cs.[A1] = "Sheet"
    cs.Rows(1).Font.Bold = True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Set cs = Nothing
End Sub

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back