Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

I have a macro button on one worksheet (call it Sheet1), which (as part of its process) tries to copy (and paste) a range of cells on another worksheet (call it OtherSheet). The macro uses row and column numbers to define the copy range. I started with a statement like this:

Please Login or Register  to view this content.

That gave me a runtime Error 1004. So I tried this:

Please Login or Register  to view this content.

This gives the same Error 1004 (on the line with the Set command). I accidentally discovered, however, that if I run the macro from within the VBA editor, while OtherSheet (not Sheet1) is the active sheet, that it runs OK. If Sheet1 is the active sheet, it fails.

So what's going on? I'm specifically referencing the worksheet to copy from (OtherSheet). Why does it care which sheet is active? How can I get it to work when OtherSheet is not the active sheet?

The Cells property is a Range type and should also be worksheet specific.

Please Login or Register  to view this content.

Try that and see if it fixes it. Also, what do you have defined as CopyRow

How is Set CopyRange defined? The variable has to be an object, Like Set CopyRange as range

Thanks, all, for the amazingly quick help!

I included only the line(s) I was having an issue with. CopyRow was previously declared as Integer and was set equal to the row number I wished to copy from (in this particular instance, 15). Likewise, CopyRange had been previously declared as Range.

Or this maybe.

Please Login or Register  to view this content.

Originally Posted by shg

It should be declared as Long. There are more rows in Excel than Integer can handle.

Good point! I will update that as well.