VBA 1004 error while running macro loop

Question

Can someone let me know why I get error 1004 with the following code?

If this is not clear, I try to loop all the sheets that are not my named sheet, and try to select a specific range and copy and paste it into the compiled "Quant Sheet"

Dim ws As Worksheet Dim x As Integer Dim y As Integer Dim a As Integer Dim b As Integer Set ws = Worksheets("Quant Sheet") x = 1 y = 3 a = 3 b = 2 Worksheets("Quant Sheet").Activate For Each ws In ActiveWorkbook.Worksheets If (ws.Name <> "Quant Sheet") Then ws.Range("A3").Select Selection.Copy Sheets("Quant Sheet").Select Cells(y, 1).Select ActiveSheet.Paste y = y + 1 End If Next ws 

Answer 1

You set WS as Worksheets("Quant Sheet") , but then use the same WS variable that will be used in your loop. This can cause problems.

Try the following:

 Dim ws As Worksheet, mainWS As Worksheet Dim x As Integer, y As Integer, a As Integer, b As Integer Set mainWS = Worksheets("Quant Sheet") x = 1 y = 3 a = 3 b = 2 For Each ws In ActiveWorkbook.Worksheets If (ws.Name <> "Quant Sheet") Then ws.Range("A3").Copy Destination:=mainWS.Cells(y, 1) y = y + 1 End If Next ws 

Basically, you want to avoid using .Select / .Activate to make sure you work more directly with the data.

Edit: FYI, you can most likely make it more dynamic without using something like y=y+1 and instead use the offset or lastRow variable, but this is a personal preference, as it will do the same thing. (I also assume that the variables x , a and b are used elsewhere in your macro ...

Answer 2

As already mentioned, you cannot .Select cell on a sheet that you did not call .Activate in the first place - this would .Activate problem, but leave you with fragile and slow calls .Select and .Activate . Instead, iterate through the Worksheets collection using a For Each loop, so you get a Worksheet object to work with each iteration:

 Sub test() Dim quantSheet As Worksheet, tempSheet as Worksheet Dim i As Integer Set quantSheet = ThisWorkbook.Worksheets("Quant Sheet") i = 3 For Each tempSheet In ThisWorkbook.Worksheets If tempSheet.Name <> quantSheet.Name Then quantSheet.Cells(i, 1).Value = tempSheet.Range("A3").Value i = i + 1 End If Next tempSheet End Sub 

---

In addition to the good answers and comments that are already provided, you can very carefully customize your code.

• Indent < . You can avoid many errors by simply sticking with a simple indent.
• Remove all unused variables (unless you use them later and show us!)
• Instead of copying and pasting, set the values ​​directly with .Value . It is faster and better.
• Avoid Select and Activate as much as possible, as already stated. This includes ActiveSheet and ActiveWorkbook
• Give your variables good, meaningful names, and your code will almost read like an ugly VBA novel. This way you will always know what is going on.

Publish your working code on Code Review Stack Exchange for a full peer review.