VBA Copy multiple sheets

[AccountantinExcel]

Good morning,

I am looking to use VBA to copy multiple sheets then paste values to a Summary sheet then remove all rows if Column A = 0. I have started with this code to copy sheets but am getting errors.

Sheet names:
G1 Sort Table
G2 Sort Table
G3 Sort Table
G4 Sort Table
G5 Sort Table
G6 Sort Table
G7 Sort Table
G8 Sort Table
G9 Sort Table
G10 Sort Table


Has anyone done this before?


"Sub SummurizeSheets()
Dim ws As Worksheet
Dim j As Integer, col As Integer


Application.ScreenUpdating = False


Sheets("Summary").Activate




For Each ws In Worksheets
If ws.Name = "G1 Sort Table" Then
ws.Range("A1:M40000").Copy


Row = Worksheets("Summary").Range("M40000").End(xlToLeft).Column + 1
Worksheets("Summary").Cells(1, Row).PasteSpecial xlPasteValues
Application.CutCopyMode = False


End If


Next ws
Columns(1).Delete
Range("A1").Activate
Application.ScreenUpdating = True
End Sub"





Thanks,

Anthony

 

[AccountantinExcel]

It worked but it does not paste values as my first formula did and also they are pasting to the right of the last one and not below. For example

It is doing this

A B C D E F
G1 G1 G1 G2 G2 G2



Should do this

A B C
G1 G1 G1

G2 G2 G2

 

[Norie]

Your original code appeared to be finding the last column in row M and using that to paste.

If you want to paste down the sheet try this.

Sub SummurizeSheets()
Dim ws As Worksheet
Dim I As Long
Dim arrSheets As Variant

    arrSheets = Array("G1 Sort Table", "G2 Sort Table", "G3 Sort Table") ' add the rest of the sheet names as needed

    Application.ScreenUpdating = False

    For I = LBound(arrSheets) To UBound(arrSheets)
        Set ws = Sheets(arrSheets(I))
        ws.Range("A1:M40000").Copy 
        Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Next I

    Application.CutCopyMode = False

End Sub

 

[AccountantinExcel]

Your original code appeared to be finding the last column in row M and using that to paste.

If you want to paste down the sheet try this.

Sub SummurizeSheets()
Dim ws As Worksheet
Dim I As Long
Dim arrSheets As Variant

    arrSheets = Array("G1 Sort Table", "G2 Sort Table", "G3 Sort Table") ' add the rest of the sheet names as needed

    Application.ScreenUpdating = False

    For I = LBound(arrSheets) To UBound(arrSheets)
        Set ws = Sheets(arrSheets(I))
        ws.Range("A1:M40000").Copy 
        Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Next I

    Application.CutCopyMode = False

End Sub

That worked!

Thank you for the assistance.

Anthony