Macro that will create summary sheet

[postjo]

I have multiple sheets that are formatted exactly the same way. I need a macro that will take the range G1:L34 from each sheet and put them one on top of the other in a summary sheet. Thanks!

 

[VoG]

This assumes that the summary sheet exists and is called Summary

Sub concat()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        ws.Range("G1:L34").Copy Destination:=Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next ws
End Sub

 

[postjo]

I inserted a sheet called "Summary" and ran the macro but nothing happens.

 

[VoG]

It worked when I tested it.

 

[postjo]

Hi the Macro almost works. However, since it copying formulas when its pastes the links are broken and it pastes as reference errors. Is there a way to modify the macro so that the links are not broken. Or, less ideally, to paste as values.

Thanks!

 

[VoG]

Try

Sub concat()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        ws.Range("G1:L34").Copy
        Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    End If
Next ws
End Sub