Combining worksheets into summary with source worksheet

[odonnke19]

Morning, I tried the combine VBA below worked perfectly however it did not list the name of the worksheet ie. 18-01-01.xls as I thought it would. Any advice? I have over 104 tab/worksheets
Sub Combine()


Dim J As Integer


On Error Resume Next


Sheets(1).Select


Worksheets.Add


Sheets(1).Name = "Combined"


Sheets(2).Activate


Range("A1").EntireRow.Select


Selection.Copy Destination:=Sheets(1).Range("A1")


For J = 2 To Sheets.Count


Sheets(J).Activate


Range("A1").Select


Selection.CurrentRegion.Select


Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select


Selection.Copy Destination:=Sheets(1).Range("A1048575").End(xlUp)(2)


Next


End Sub

 

[DanteAmor]

Check if the following is useful to put the name of the sheet in column A

Sub Combine()
  Dim J As Integer, lr As Long, lr2 As Long
  Sheets(1).Select
  Worksheets.Add
  Sheets(1).Name = "Combined"
  Sheets(2).Activate
  Range("A1", Cells(1, Columns.Count).End(1)).Copy Sheets(1).Range("B1")
  For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    lr = Sheets(1).Range("A" & Rows.Count).End(3)(2).Row
    Selection.Copy Destination:=Sheets(1).Range("B" & lr)
    lr2 = Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Sheets(1).Range("A" & lr & ":A" & lr2).Value = Sheets(J).Name
  Next
End Sub

 

[odonnke19]

Thanks, works great!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.