Copy range A:7 - Z200 from all sheets and paste into summary sheet

Thanks for the code Tiger. I am getting an error though and when I debug it is highlighting the section "ws.Range(strRange).Copy Sheets(wsDestNm).Cells(Rows.Count, "A").End(xlUp).Offset(1)". Do you know why by chance? Thanks

Originally Posted by tigeravatar

cheesiepoof05,

Welcome to the forum!
Here's some code to get you started. In your title you use A7:Z200 but in the post you state A7:X200. The code uses A7:X200, change as needed. the wsDestNm is the worksheet Destination Name, so it should be the name of the Summary Sheet.

Sub tgr()
    
    Const strRange As String = "A7:X200"
    Const wsDestNm As String = "Summary"
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDestNm Then ws.Range(strRange).Copy Sheets(wsDestNm).Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Next ws
    
End Sub