Range.Copy (Destination)
Hi
Can someone please explain where I'm going wrong with this simple two line chunk of code? B7 is the currently selected cell on the active sheet. I've assigned the cell reference for D10 to a variable named copycell by using the following line: Let copycell = Selection.Offset(3,2).Address(False,False) All I want to be able to do is copy the contents of D10 (an = sum() formula) to E10 using the Range.Copy ([Destination]) method but if I use Range (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error 1004 (Copy method of Range class failed). However, if I use a range object offset from the current cell (B7) as the destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes the relative formula from D10 into E10 as required. What am I doing wrong? Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF To Reply ** |
Range.Copy (Destination)
Sub Tester1()
Range("B7").Select Let copycell = Selection.Offset(3, 2).Address(False, False) Range(copycell).Copy Range(copycell).Offset(0, 1) End Sub worked fine for me. I believe you had an extra pair of parenthesis around Range(copycell).Offset(0, 1) so it was Range(copycell).Copy (Range(copycell).Offset(0, 1)) That extra pair of parentheses causes the range reference to be dereferenced, so it becomes the wrong type argument to the copy command. I would expect your second example to have similar problems, but perhaps you actually omitted the second set of parentheses and avoided the problem. But I definitely get your error message if I enclose the second argument in parentheses. -- Regards, Tom Ogilvy "Garry Douglas" wrote in message ... Hi Can someone please explain where I'm going wrong with this simple two line chunk of code? B7 is the currently selected cell on the active sheet. I've assigned the cell reference for D10 to a variable named copycell by using the following line: Let copycell = Selection.Offset(3,2).Address(False,False) All I want to be able to do is copy the contents of D10 (an = sum() formula) to E10 using the Range.Copy ([Destination]) method but if I use Range (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error 1004 (Copy method of Range class failed). However, if I use a range object offset from the current cell (B7) as the destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes the relative formula from D10 into E10 as required. What am I doing wrong? Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF To Reply ** |
Range.Copy (Destination)
Tom
Thanks for the tip. I'll give it a try. I also discovered through trial and error (a.k.a. Help) that the Destination part of the Range.Copy (Destination) argument could have been expressed as Destination:=Range(copycell).Offset(0, 1) which seems to proves your double parenthesis theory :-) I assume that the "Destination:=" qualifier is optional. Regards Garry "Tom Ogilvy" wrote in message ... Sub Tester1() Range("B7").Select Let copycell = Selection.Offset(3, 2).Address(False, False) Range(copycell).Copy Range(copycell).Offset(0, 1) End Sub worked fine for me. I believe you had an extra pair of parenthesis around Range(copycell).Offset(0, 1) so it was Range(copycell).Copy (Range(copycell).Offset(0, 1)) That extra pair of parentheses causes the range reference to be dereferenced, so it becomes the wrong type argument to the copy command. I would expect your second example to have similar problems, but perhaps you actually omitted the second set of parentheses and avoided the problem. But I definitely get your error message if I enclose the second argument in parentheses. -- Regards, Tom Ogilvy "Garry Douglas" wrote in message ... Hi Can someone please explain where I'm going wrong with this simple two line chunk of code? B7 is the currently selected cell on the active sheet. I've assigned the cell reference for D10 to a variable named copycell by using the following line: Let copycell = Selection.Offset(3,2).Address(False,False) All I want to be able to do is copy the contents of D10 (an = sum() formula) to E10 using the Range.Copy ([Destination]) method but if I use Range (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error 1004 (Copy method of Range class failed). However, if I use a range object offset from the current cell (B7) as the destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes the relative formula from D10 into E10 as required. What am I doing wrong? Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF To Reply ** |
Range.Copy (Destination)
arguments can be passed by name or by position. If the Destination:= is not
use, you are passing by position. -- Regards, Tom Ogilvy "Garry Douglas" wrote in message ... Tom Thanks for the tip. I'll give it a try. I also discovered through trial and error (a.k.a. Help) that the Destination part of the Range.Copy (Destination) argument could have been expressed as Destination:=Range(copycell).Offset(0, 1) which seems to proves your double parenthesis theory :-) I assume that the "Destination:=" qualifier is optional. Regards Garry "Tom Ogilvy" wrote in message ... Sub Tester1() Range("B7").Select Let copycell = Selection.Offset(3, 2).Address(False, False) Range(copycell).Copy Range(copycell).Offset(0, 1) End Sub worked fine for me. I believe you had an extra pair of parenthesis around Range(copycell).Offset(0, 1) so it was Range(copycell).Copy (Range(copycell).Offset(0, 1)) That extra pair of parentheses causes the range reference to be dereferenced, so it becomes the wrong type argument to the copy command. I would expect your second example to have similar problems, but perhaps you actually omitted the second set of parentheses and avoided the problem. But I definitely get your error message if I enclose the second argument in parentheses. -- Regards, Tom Ogilvy "Garry Douglas" wrote in message ... Hi Can someone please explain where I'm going wrong with this simple two line chunk of code? B7 is the currently selected cell on the active sheet. I've assigned the cell reference for D10 to a variable named copycell by using the following line: Let copycell = Selection.Offset(3,2).Address(False,False) All I want to be able to do is copy the contents of D10 (an = sum() formula) to E10 using the Range.Copy ([Destination]) method but if I use Range (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error 1004 (Copy method of Range class failed). However, if I use a range object offset from the current cell (B7) as the destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes the relative formula from D10 into E10 as required. What am I doing wrong? Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF To Reply ** |
All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com