VBA 1004 Error on Loop execution of Macro

Question

Can anybody give me a sense of why I'd be receiving a 1004 error on the following code?

If it's not clear, I'm trying to loop all sheets that are not my named sheet and try to select a particular range and copy and paste it to 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

You cannot select a cell until you activate its parent worksheet. Use `ws.activate` before `AB$2:AE" & lastRowSF`. Once it's working take a look at [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — , May 12 '17 at 21:15

BruceWayne · Accepted Answer · 2017-05-13T22:04:38.033

6

You set WS as Worksheets("Quant Sheet") but then use that same variable ws to use in your loop. That may be causing the issue.

Try this:

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

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

Edit: FYI you can likely further make this more dynamic by not using something like y=y+1 and instead use offset, or a lastRow variable, but that's personal preference as it'll accomplish the same thing. (I'm also assuming the x, a, and b variables are used elsewhere in your macro...

edited May 13 '17 at 22:04

answered May 12 '17 at 21:14

BruceWayne

22,923
15
65
110

1

`ws.Range("A3").Copy Destination:=mainWS.Cells(y, 1)` .Paste is a worksheet method, not a range method. – May 12 '17 at 21:17
@Jeeped - D'oh, thanks for fixing that, I just noticed it too. – BruceWayne May 12 '17 at 21:21
Well, I couldn't upvote you in the original form and I promised my dog a cheeseburger so I have to be going. – May 12 '17 at 21:23
1

@Jeeped - Wait ...I thought you *were* the dog! – BruceWayne May 12 '17 at 21:47
@jeeped I think I'm running into that problem again. however because I need a special paste (transpose) I don't know how to deal with it. See my updated code and please feel free to show me the light if you would be so kind! – Bez May 12 '17 at 23:59
3

That is a new question. Reverse your edit and create a new question. In the new question, specify if you require values only or values and formats. You cannot transpose formulas without invoking #REF! errors. Besides, what's wrong with Batman? (oops!) – May 13 '17 at 00:32

score 3 · Answer 2 · edited May 23 '17 at 12:10

As was already stated, you can't .Select a cell on a worksheet you haven't called .Activate on first - that would fix the problem, but leave you with frail & slow .Select and .Activate calls everywhere. Instead, iterate the Worksheets collection with 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

Further to the good answers and comments already provided, you can neaten up your code a lot.

Indentation is key. You can avoid loads of errors just by sticking to simple indentation
Remove of all those unused variables (unless you're using them later and haven't shown us!)
Rather than copying and pasting, set your values directly using .Value. It's quicker and better
Avoid Select and Activate as much as possible, as has already been pointed out. That includes ActiveSheet and ActiveWorkbook
Give your variables good, meaningful names and your code will almost read like a geeky VBA novel. That way you'll always know what's going on.

Post your working code on Code Review Stack Exchange for a full-blown peer review.

I realize I have a lot of cleaning up to do! – Bez May 12 '17 at 23:58 — Bez, May 12 '17 at 23:58

VBA 1004 Error on Loop execution of Macro

2 Answers2