Copy data from 4 identical sheets to single sheet

[glynn1969]

Hello (i have simplified my query a little)

I have 4 sheets with data in rows, on each sheet in column A I have an identifier eg "X".

My want is to have a VBA which will copy data from sheet 1, all rows with the X identifier to sheet 5 - with no spaces between rows.

Then to look at sheet 2 and again move all rows with the X identifier to sheet 5 directly underneath the sheet1 data - again leaving no rows.....repeat for sheet 3 and sheet4.


So sheet 5 will have all data from sheets 1-4 where the X identifier was identified into a single worksheet with no spaces between the rows.

Here's hoping one of you clever people can help.

Thank you

 

[mrshl9898]

Are there other sheets in the book?

If not, try:

Sub Test()


Dim rownum As Long
Dim lastrow As Long


rownum = 2


For Each Sheet In ThisWorkbook.Sheets
If Sheet.Name <> "Sheet5" Then
Sheet.Select
Set sht = ActiveSheet
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
For Each cell In Sheet.Range("A1:A" & lastrow)
    If cell.Value = "X" Then
        rownum2 = cell.Row
        Rows(rownum2).Copy Sheets("Sheet5").Rows(rownum)
        rownum = rownum + 1
    End If
Next
End If
Next Sheet


End Sub