VBA - Consolidating spreadsheets including tab name

[Clare1805]

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!

 

[onlyadrafter]

Hello,

is this working as expected?

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
            Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Value = Sheets(Sheet.Index).Name
            Sheet.UsedRange.Copy Destination:=Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
            lastrow = lastrow + RowCount
        End If
    Next Sheet
End Sub

 

[Andrew Poulsom]

Here's an example that also copies the sheet name:

http://www.rondebruin.nl/copy2.htm

 

[onlyadrafter]

darn it!