1

I've got a macro assigned to two buttons, each on a different worksheet: "Print Page 1" and "Optimization". The macro takes information from the first to know how many times to copy a range the second.

When called while "Optimization" is active, the code works perfectly, but when called with the "Print Page 1" worksheet active, a "1004 error: Application-defined or object-defined error" is produced on the line that copies the ranges. See below:

Sub Range_Tester()

Dim PrintRow, WallQty

WallQty = Worksheets("Print Page 1").Cells(24, 4)
PrintRow = 20

While WallQty > 1
    Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))
    WallQty = WallQty - 1
    PrintRow = PrintRow + 1
Wend

End Sub

I think the problem is my range syntax; if I replace

Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))

with

Worksheets("Optimization").Cells(PrintRow - 1, 2).Copy Worksheets("Optimization").Cells(PrintRow, 2)
Worksheets("Optimization").Cells(PrintRow - 1, 3).Copy Worksheets("Optimization").Cells(PrintRow, 3)
Worksheets("Optimization").Cells(PrintRow - 1, 4).Copy Worksheets("Optimization").Cells(PrintRow, 4)

The info copies flawlessly regardless of which worksheet is active.

My apologies for asking what is probably a stupidly simple question, I just haven't had any luck finding info on properly defining ranges that change locations.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Approx.
  • 13
  • 5
  • You must apply the same parent to the `Cells` in the `Range` as the `Range`: `Worksheets("Optimization").Range(Worksheets("Optimization").Cells(...` – Scott Craner Dec 03 '18 at 18:14
  • https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams Dec 03 '18 at 19:08

1 Answers1

2

Cells being unqualified, implicitly refers to ActiveSheet, because that code isn't written in a worksheet module. If it was, then unqualified Cells would be like Me.Cells. Anywhere else, it's ActiveSheet.Cells.

Always explicitly qualify Worksheet member calls with a proper Worksheet object, and you'll avoid this problem.

Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))

Here, you're invoking .Range against Worksheets("Optimization"), but then parameterizing it with Cells(PrintRow - 1, 2), which may belong to another sheet (whatever ActiveSheet is). When it does, things blow up.

Capture the Worksheets("Optimization") reference into a With block variable (or a plain old local variable):

With Worksheets("Optimization")
    .Range(.Cells(PrintRow - 1, 2), .Cells(PrintRow - 1, 4)).Copy .Range(.Cells(PrintRow, 2), .Cells(PrintRow, 4))
End With

Notice the . dereferencing operator, which makes .Cells work off Worksheets("Optimization") like the .Range call, ensuring everything is pulled from the same sheet.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Wooooowww! That makes sense! It surprises me that the Worksheet qualification would not extend to `.Cells` , though; qualifying a range in one worksheet and cells a different worksheet hurts my newbie brain, but it must have some application. – Approx. Dec 03 '18 at 19:25
  • 2
    @Approx. (great username!) grab yourself a copy of [Rubberduck](https://github.com/rubberduck-vba/Rubberduck). It will do wonders for your coding skills. I'm a very happy user (and unofficial QA tester). Mathieu is the project owner and a heavy contributor. – FreeMan Dec 03 '18 at 19:32