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
    [A2]B1
    [B2]37


    (New Sheet in position 9) 0421XXXX
    [A2]B2
    [B2]3


    (New Sheet in position etc.) 0422XXXX
    [A2]B3
    [B2]40



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



    Thank you for any help!

    You could start with something like this:

    Code
    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
    Next


    HTH,
    Eric

    HTH,
    Eric :drunk:

Participate now!

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

Register Yourself Login