Link to home
Start Free TrialLog in
Avatar of aeolianje
aeolianjeFlag for United States of America

asked on

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
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

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

Avatar of aeolianje

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
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is perfect!!!!  Thanks so much for your help.

je