Search code examples
excelvbacopy-pasteworksheet-function

selecting multiple worksheet with prefix


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

From these sheets I wanted to copy range -example (A13:L40) and paste them all together in another tab "summary".

Any help or suggestion is highly appreciated

Below is the macro I have created so far, still not sure how can i add 2017 as well.

Sub Macroif()
    Dim sheet As Worksheet

    Sheets("Summary").Activate

    For Each sheet In Worksheets
        If (sheet.Name Like "2018*") Then
            sheet.Select
            sheet.Range("A13:L40").Copy
            Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

        End If
    Next sheet

End Sub

Solution

  • You can loop through the sheets as well, without selecting the sheet.

    Sub Macroif()
        Dim sh As Worksheet
    
        For Each sh In Worksheets
            If sh.Name <> "Summary" Then
                If sh.Name Like "2017*" Or sh.Name Like "2018*" Then
                    With sh
                        .Range("A13:L40").Copy
                        With Worksheets("Summary")
                            .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
                        End With
                    End With
                End If
    
            End If
        Next sh
    
    End Sub