Copy and paste problem

[jerl100]

This is probably really simple, but I'll ask anyway.

Having looked in the archives yesterday to find a simple piece of code to copy a cell and paste it over a range, I have the following code that works in a spreadsheet by itself:

Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5", Range("D5").Offset(0, 30))

The 30 will (eventually) be replaced by a variable defined elsewhere in the main piece of code.

However, when it is pasted into the main spreadsheet, even as a standalone macro rather than integrated in the other code, it almost always fails with a 'Run-time error '1004': Application-defined or object-defined error' (this caused me no end of problems yesterday as I was attemting to use it in the main spreadsheet rather than a stand-alone one, so kept assuming that the code was failing completely).

I know I can use variables to define the range, but was wondering if there is a simple alteration I could make to make this code work, as it's far simpler in structure.

Cheers

James

 

[Andrew Poulsom]

Your code worked fine when I tested it.

How are you running it? What happens if you copy/paste manually?

 

[GlennUK]

It's more likely that your code simply needs a small alteration than there being abny problems with variables ...

Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5").Resize(1, 30)

 

[Andrew Poulsom]

It's more likely that your code simply needs a small alteration than there being abny problems with variables ...

Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5").Resize(1, 30)

Ah yes, fails if Sheet1 is not the ActiveSheet:

Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5", Sheets("Sheet1").Range("D5").Offset(0, 30))

but your solution is better Glenn.

 

[jerl100]

Was in the middle of composing a response to the first reply, when the second one comes along and solves it.

Thank you very much for your help, both of you.

James

 

[GlennUK]

My pleasure

Thanks Andrew ... yes, I prefer that way of resizing a selection ( usually ... but as you know, there are always exceptions to any rule ).