VBA Copy multiple sheets

AccountantinExcel

New Member
Joined
Sep 10, 2018
Messages
10
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Anthony

What errors are you getting?

Where do you want to copy to in the Summary sheet?
 
Upvote 0
I am copying in to Cell A2. I got it to work but now having issue going to next sheet. The error that I am is "Compile Error Expected list separator or end of statement at the "ws.Name = "

Next ws.Name = "G2 Sort Table" Then
ws.Range ("A1:M5000").Copy
 
Upvote 0
You have the For here.:eek:
Code:
For Each ws In Worksheets
Are you getting an error message, e.g. Next without For?

Can you post your code?

Also, why do you have this?
Code:
If ws.Name = "G1 Sort Table" Then
That would mean the only sheet data would be copied from would be 'G1 Sort Table'.:)
 
Upvote 0
Yes, I am getting the error message Next without For.

I have G1 Sort Table because that is where I want the Macro to start. I have a total of 20 sheets but only need the date from 10 of them.



Code:
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
 
Upvote 0
Do you want to loop through the sheets of interest in a specific order?

If you do you would need to put them in an array in the order you want.

Then instead of looping through all the sheets, as the code currently is, you would loop through the array.
 
Upvote 0
I am not well versed in VBA code. Would it look something like this?

Code:
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 = Array "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" 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 Sub
 
Upvote 0
Not quite.:)

I think you need to forget about the If statement, in your original code it would restrict the code to working on one worksheet, 'G1 Sort Table', and though you could adapt it for multiple sheets it wouldn't be the best approach.

Try this instead.
Code:
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").Cells(1, Columns.Count).End(xlToLeft).Offset(,1)
    Next I

    Application.CutCopyMode = False

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top