Combine Excek worksheets

aeolianje
aeolianje used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
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
Attached is updated sheet with additional macro code supplied
Macro-test.xlsm

Author

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

je

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial