Copy and paste problem

jerl100

New Member
Joined
Nov 10, 2005
Messages
11
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Your code worked fine when I tested it.

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

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
It's more likely that your code simply needs a small alteration than there being abny problems with variables ...
Code:
Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5").Resize(1, 30)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
GlennUK said:
It's more likely that your code simply needs a small alteration than there being abny problems with variables ...
Code:
Sheets("Sheet1").Range("D5").Copy Sheets("Sheet1").Range("D5").Resize(1, 30)

Ah yes, fails if Sheet1 is not the ActiveSheet:

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

but your solution is better Glenn.
 

jerl100

New Member
Joined
Nov 10, 2005
Messages
11
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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 ).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top