Pull Several rows from each tab into a summary sheet

[djb113]

Hello,

I have a spreadsheet where I want to pull rows 11-17 from each tab into a summary sheet. All of the tabs have the same structure, but different data for each market, so I'm trying pull it all into one data source. Can anyone advise on how I can do this with VBA? Thanks!

 

[DanteAmor]

Change in the macro "summary" to the name of your summary sheet

Sub Fill_summary()
    Dim ws As Worksheet, sh As Worksheet, n As Long
    
    Application.ScreenUpdating = False
    
    Set ws = Sheets("[COLOR=#0000ff]summary[/COLOR]")
    ws.Rows("2:" & Rows.Count).ClearContents
    n = 2
    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            sh.Rows("11:17").Copy
            ws.Range("A" & n).PasteSpecial xlPasteValues
            n = n + 7
        End If
    Next
    
    Application.CutCopyMode = True
    Application.ScreenUpdating = True
    
    MsgBox "End"
End Sub