Thread: Copying data from multiple worksheets to a master worksheet

Hi, I'm a beginner at Excel, so please bear with me.

I have a workbook with multiple worksheets. Each worksheet is a set o data from a certain year. I want to create a macro that automatically copies the data from these worksheets into a master worksheet that can easily be used to make a pivot table. How can I do this? (I included a sample workbook in the attachments.)

If every sheet has the exact same column headers, all the data is already in the correct place and you just want "Summary" report created, then this basic macro does that:

Option Explicit

Public Function SheetExists(SName As String, Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
    If Wb Is Nothing Then Set Wb = ThisWorkbook
        SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

Sub SummaryReport()
'JBeaucaire (6/21/2009)
'Copy rows of data from multiple sheets to one matching one ID #
Dim ws As Worksheet, LR As Long, NR As Long

'Setup
    If SheetExists("Report") Then
        Sheets("Report").Range("A2:Z" & Rows.Count).ClearContents
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Report"
        Sheets(1).Rows(1).EntireRow.Copy Sheets("Report").Range("A1")
    End If

    NR = 2

'Copy data to Report
    For Each ws In Worksheets
        If ws.Name <> "Report" Then
            ws.Activate
            LR = Range("A" & Rows.Count).End(xlUp).Row
            Range("A2:Z" & LR).Copy Sheets("Report").Range("A" & NR)
            NR = NR + LR - 1
        End If
    Next ws

End Sub

Run the SummaryReport macro (press Alt-F8)

If you run it successively, it erases the existing report and creates a new one.

If you don't want the old report erased but want new data appended to the bottom of the existing report, comment out or erase the "optional" line of code in the macro.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)