I have a basic understanding of VBA and am trying to create a macro that will consolidate all worksheets within a workbook into one tab.
I have the found the following code which consolidates all the data but I need it to also copy the name of the worksheet alongside the data from that sheet - can anyone help?
Sub mergeallsheets2one()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
End If
Next Sheet
End Sub
Thanks in advance!
I have the found the following code which consolidates all the data but I need it to also copy the name of the worksheet alongside the data from that sheet - can anyone help?
Sub mergeallsheets2one()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
End If
Next Sheet
End Sub
Thanks in advance!