Running Macro on multiple worksheets using prefix and copy data to other worksheet

Question

I am completely new in VBA , please excuse the silly questions ;)

Background
I am working on a worksheet with multiple tabs although I need to copy data from worksheets that start with only "2018 and 2017" and then paste it to summary tab.

Details
At Top of These sheet are headers and other calculation which i don't need to copy.(refer Row# 1 to 10 in attached snap shot)
From these sheets I wanted to copy range which is starting from row 13 but this row# can change sheet to sheet, so i have created two variable - first select Cell(L1) and go XL down Second to select Range("L1048576").End(xlUp).Address from there I am selecting End xlToLeft to select the whole range and copy it-

Problem
I am not able to run this code using variable to select the range, if i select data range using cell reference example("A13:L26")its works fine but getting error while using variable to select the range. Also is there a way by which I can exclude the headers?

Below is my codes

Sub NEWWORK()
    Dim sheet As Worksheet
    Dim a As String
    Dim B As String

    a = Range("L1").End(xlDown).Address
    B = Range("L1048576").End(xlUp).Address

    Sheets("Summary").Activate

    For Each sheet In Worksheets
        If (Left(sheet.Name, 4) = "2018") Or (Left(sheet.Name, 4) = "2017") Then
            sheet.Select
            sheet.Range(a, B).Select
            Range(Selection, Selection.End(xlToLeft)).Copy

            Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).PasteSpecial (xlPasteValues)
        End If
    Next sheet
End Sub

Tim Williams Tim Williams · Accepted Answer · 2018-08-10T00:44:01

You need to find the range on each sheet - you need to do that inside the loop:

Sub NEWWORK()
    Dim sheet As Worksheet
    Dim rng As Range

    Sheets("Summary").Activate

    For Each sheet In Worksheets
        If (Left(sheet.Name, 4) = "2018") Or (Left(sheet.Name, 4) = "2017") Then

            With sheet
                Set rng = .Range(.Range("L1").End(xlDown).Offset(1, 0), _
                                  .Cells(Rows.Count, "L").End(xlUp))
            End With

            With Worksheets("Summary")
                .Cells(Rows.Count, 1).End(xlUp).Resize(rng.Rows.Count, 1).Value = rng.Value
            End With

        End If
    Next sheet

End Sub

Running Macro on multiple worksheets using prefix and copy data to other worksheet

1 Answers