Solution #1: Your statement
.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues
does not refer to a proper Range
to act upon. Instead,
.Range(.Cells(RangeStartRow, RangeStartColumn), .Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues
does (and similarly in some other cases).
Solution #2:
Activate Worksheets("Cable Cards")
prior to using its cells.
Explanation:
Cells(RangeStartRow, RangeStartColumn)
(e.g.) gives you a Range
, that would be ok, and that is why you often see Cells
used in this way. But since it is not applied to a specific object, it applies to the ActiveSheet
. Thus, your code attempts using .Range(rng1, rng2)
, where .Range
is a method of one Worksheet
object and rng1
and rng2
are in a different Worksheet
.
There are two checks that you can do to make this quite evident:
Activate your Worksheets("Cable Cards")
prior to executing your Sub
and it will start working (now you have well-formed references to Range
s). For the code you posted, adding .Activate
right after With...
would indeed be a solution, although you might have a similar problem somewhere else in your code when referring to a Range
in another Worksheet
.
With a sheet other than Worksheets("Cable Cards")
active, set a breakpoint at the line throwing the error, start your Sub
, and when execution breaks, write at the immediate window
Debug.Print Cells(RangeStartRow, RangeStartColumn).Address(external:=True)
Debug.Print .Cells(RangeStartRow, RangeStartColumn).Address(external:=True)
and see the different outcomes.
Conclusion:
Using Cells
or Range
without a specified object (e.g., Worksheet
, or Range
) might be dangerous, especially when working with more than one Sheet
, unless one is quite sure about what Sheet
is active.