Am I going crazy? Simple thing not working error 1004

[CobertRannon]

Hello,

So I have the simplest line not working. The sheet name is right and what I am copying is a formula that I will be doing a paste special with. It works on another sheet. Why on earth is this returning a application-defined or object defined error 1004?

Please Login or Register  to view this content.

Thank you so much for your help. Have a nice day.

-Rob

[AB33]

Nothing wrong with that line, but if you are trying to copy and paste, you should have two separate lines, one for the copy line and second for the paste line.

[CobertRannon]

I'm just posting the line that doesn't work. When I do this on another workbook it works fine and I can paste manually the select

[AB33]

Could you please the entire code? I tested it and did not get any error.

[MarvinP]

Hi Colbert,

Try using a named range in your code, like:

Range("MyRange")
instead of:
Range(Cells(6, 2), Cells(35, 2))

[shg]

Why on earth is this returning a application-defined or object defined error 1004?

Please Login or Register  to view this content.

Because both of the Cells objects refer to cells on the active worksheet.

Please Login or Register  to view this content.

[Kyle123]

Or fully qualify your cell references, currently they're working on the active sheet

[CobertRannon]

Wow,

I don't know why this took so long but the last comment worked by my using

Please Login or Register  to view this content.

This seemed like it was way too hard. I should be able to refer to another sheet with just one declaration of the name of the sheet but as long as it works as I am getting close to a deadline. Thank you for the responses.

[Kyle123]

That's what shg's code does

[CobertRannon]

The thing as well is that it easily works with the A1 notation but I need to have this in cells format so I can use numbers for row references.... Why was this so much harder with cells formatting?

[shg]

It's no harder; Cells, like Range, refers to the active worksheet if not qualified.

[Kyle123]

It's not to do with cells formatting it's to do with implicit references. Whenever you write Range or Cells etc without specifying the object (which sheet it is) Excel assumes you want to use the active sheet, so your code actually does this:

Please Login or Register  to view this content.

When you pass a string ("A1" for example) to the Range function, that isn't the same as passing cells - cells are objects.

[CobertRannon]

Thank you so much for the clarification. I really thought I was going crazy with this one. I understand fully now.

Have a nice day everyone.

[MarvinP]

@shg,

I went crazy many times with this problem and I needed your answer back then to keep my sanity. Great ANSWER!!!

[shg]

The one fillip, Marvin, is that Range need not be qualified if the two corner cells are, e.g., this is fine:

Please Login or Register  to view this content.

As Kyle points out, though, you need to qualify if the first cell is a string reference, though:

Please Login or Register  to view this content.