title: Include source sheet name to an output sheet
body: <p>I'm trying to consolidate multiple sheets into one sheet and add a new column for the final "Combined" sheet. The new sheet should have a column named "Source" with the sheet name from where the rows behind it are copied.</p>
  
  <pre><code>Sub Final()
  Path = " "
  Filename = Dir(Path &amp; "*.csv")
  
  Do While Filename &lt;&gt; ""
    Workbooks.Open Filename:=Path &amp; Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
       Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
       Workbooks(Filename).Close
       Filename = Dir()
  Loop
  
  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("A65536").End(xlUp)(2)
  Next
  End Sub
  </code></pre>
  
  <p>thanks in advance for your help guys :)</p>