Combine Multiple Worksheets

[Nanogirl21]

I have 1 workbook with 8 worksheets. All worksheets are set up in the same format using columns A - Y. Is there any way that I can easily combine all worksheets into 1? On the new worksheet in column A I'd like the name of the orginal worksheet and the actual data to be from coumn B - Z. Thank you for the help.

 

[Nanogirl21]

Hello Nanogirl,

Try it as follows:-

Sub Test()

        Dim ws As Worksheet
        Dim sh As Worksheet: Set sh = Sheets("Combined")
        Dim lr As Long, sr As Long

Application.ScreenUpdating = False

For Each ws In Worksheets
        ws.[A1:L1].Copy sh.[B1]  '---->Change L1 to whatever your last column is (V1 I think?)
        sh.[A1] = "Source Sheet"
        If ws.Name <> "Combined" Then
        ws.[A1].CurrentRegion.Offset(1).Copy sh.Range("B" & Rows.Count).End(3)(2)
        lr = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
        sr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1
        sh.Range("A" & sr & ":A" & lr) = ws.Name
        End If
Next ws

sh.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

I haven't added your create sheet line of code above. I'll leave that to you.

I hope that this helps.

Cheerio,
vcoolio.

This didn't work. I got "source sheet" for every cell in the new Combined worksheet.

 

[Nanogirl21]

Actually nevermind I got it to work. Thank you!

 

[vcoolio]

You're welcome Nanogirl.

Cheerio,
vcoolio.