Trying to get cell data from specific sheets

  • I have varying number of sheets in a workbook.

    All new sheets (0420xxxx) are created and sorted through vb between sheets named:
    0000a000 & 9999z999

    I want to be able to copy cells A2:B2 on all sheets between to Summary Sheets.

    (Existing Sheet) Summary
    I wish to copy cells A2:B2 on all sheets between 0000a000 & 9999z999 to multiple rows as shown here:

    [A3] B1 [B3] 37
    [A4] B2 [B4] 3
    [A5] B3 [B5] 40

    (Existing Sheet in position 7) 0000a000 (Blank)

    (New Sheet in position 8) 0420XXXX

    (New Sheet in position 9) 0421XXXX

    (New Sheet in position etc.) 0422XXXX

    (Existing Sheet in last position(currently 11 but changes)) 9999z999 (Blank)

    Thank you for any help!

  • You could start with something like this:

    Dim sht As Excel.Worksheet
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name <> "Summary" Then
                sht.[a2:b2].Copy Sheets("Summary").[A65536].End(xlUp)(2)
            End If


    Eric :drunk:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!