asked on 5/21/2012

Combine Excek worksheets

I maintain weekly time reporting worksheets (within a single Excel file) and want to combine them into one worksheet in order to summary information over the year.

The column headings from each sheet are consistent.

I would like to capture the sheet name as a new column in order to differentiate the data once combined.

Attached is a sample.

Thanks for your help,
je
Weekly-Activity-Tracking---sampl.xls

Microsoft Excel

Last Comment

aeolianje

5/22/2012

Ken Butters

5/21/2012

You didn't specify what information from each sheet you expected to see in the New Summary sheet... but the following routine will create a new summary sheet, and loop through all other existing sheets, and add the tab/sheet name in the top row... 1 per column.

Sub Summarize()

    Dim nextColumn As Long
    Dim ws As Worksheet
    Dim newWorksheet As Worksheet
    
    Set newWorksheet = Sheets.Add
    newWorksheet.Name = "Year End Summary"
    nextColumn = 0
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Year End Summary" Then
            nextColumn = nextColumn + 1
            newWorksheet.Cells(1, nextColumn) = ws.Name
        End If
    Next
End Sub

Open in new window

aeolianje

5/22/2012

ASKER

I would like the contents of each sheet included in the summary sheet -- with an extra column indicated which sheet it came from. Attached is a sample spreadsheet showing the "Year End Summary" spreadsheet resulting from the code -- and a 'Desired Results' spreadsheet showing what I'm really looking for. Hope that clarifies.

Thanks,
je
Macro-test.xlsm

ASKER CERTIFIED SOLUTION

Ken Butters

5/22/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

aeolianje

5/22/2012

ASKER

This is perfect!!!! Thanks so much for your help.

je