combine data from 50 states into one sheet

[domineaux]

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

Thanks

 

[pboltonchina]

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

'Setup
Set cs = ActiveWorkbook.Sheets("Consolidate")
cs.Cells.ClearContents
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
                Else
                    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
            Else
                cs.Range("A" & NR).PasteSpecial xlPasteValues
            End If
            
            NR = cs.Range("A" & cs.Rows.Count).End(xlUp).Row + 1
        End If
    Next ws

'Sort
    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

'Cleanup
    If sName Then cs.[A1] = "Sheet"
    cs.Rows(1).Font.Bold = True
    cs.Cells.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    cs.Activate
    Range("A1").Select
    Set cs = Nothing
End Sub