Complex Copy Line Works in Parts but Not Complete

[dhunton]

I have a long command that works when I test parts of it, but not when I combine it. Any information on what I'm doing wrong would be appreciated.


This is what I want to do:

Sub TransferData()

Dim TargetSheet as Worksheet
Dim SourceSheet as Worksheet
Dim SizeOfRange as Integer

SourceSheet.Range("C2", Range("C2").Offset(SizeOfRange, 0)).Copy _
TargetSheet.Range("A2", Range("A2").Offset(SizeOfRange, 0))

End Sub


(Note that I've tried it all on one line and split, but put the split in here for clarity.) When I just activate each of the Ranges, they work fine:

SourceSheet.Activate
Range("C2", Range("C2").Offset(SizeOfRange, 0)).Activate
TargetSheet.Activate
Range("A2", Range("A2").Offset(SizeOfRange, 0)).Activate


And if I try to copy just the first cell, it works fine:

SourceSheet.Range("C2").Copy TargetSheet.Range("A2")


When I run the entire line, I get a "Run-time error '1004': Method 'Range' of object '_Worksheet' failed" message.

I am completely at a loss. Usually breaking things out and testing each part tells me which part I have wrong, but not this time. And a search for Run-time error '1004' was no help, as it seems to be a pretty generic error.

Thanks for all the help you guys give everyone every day!

 

[JLGWhiz]

I have a long command that works when I test parts of it, but not when I combine it. Any information on what I'm doing wrong would be appreciated.

SourceSheet.Activate
Range("C2", Range("C2").Offset(SizeOfRange, 0)).Activate
TargetSheet.Activate
Range("A2", Range("A2").Offset(SizeOfRange, 0)).Activate

I don't see where you have initialized the 'SizeOfRange' variable to give it a value.

 

[dhunton]

Sorry, that actually gets calculated above, and I've checked in Break Mode that the value is correct (with my test data, it's actually 8, but it will vary with the real data).

 

[JLGWhiz]

I find it best to do away with the 'Activate' and just use direct coding like

try this
[Code]
SourceSheet.Range("C2", Range("C2").Offset(SizeOfRange, 0)).'Do something

If you use Activate, then you have to activate one object at a time all the way from top to bottom or you get the error message. If you just use the parent/child stuctured statement (tied with the period) then it works much better and reduces the flicker and flash.

The second copy statement looks OK, not sure about the first one.
Try this

SourceSheet.Range("C2", Range("C2").Offset(SizeOfRange, 0)).Copy _
 TargetSheet.Range("A2")

 

[dhunton]

As a note, I only used the Activate so I could test that I was referencing the range correctly. I prefer not to use it, just like you do.

What I am trying to do is copy a range of cells (from C2 to the end of the data in the C column) from the SourceSheet to the same rows in column A in the TargetSheet.

Everything before the first "End Sub" in my initial post was only done for testing. Sorry if that was a bit confusing.

Try this

SourceSheet.Range("C2", Range("C2").Offset(SizeOfRange, 0)).Copy _
 TargetSheet.Range("A2")[/QUOTE]

This is copied directly from my subroutine, and still fails with the same 1004 error:

SourceSheet.Range("C2", Range("C2").Offset(SizeOfRange, 0)).Copy TargetSheet.Range("A2")


In Break Mode, I can see that SizeOfRange is set to 8 with the test data. SourceSheet and TargetSheet were used in previous lines in the subroutine without problem.


Sometimes, I have a love-hate relationship with programming, and when stuff like this comes up, I remember why I hate it.

 

[JLGWhiz]

Try it this way

SourceSheet.Range("C2").Resize(SizeOfRange + 1, 1).Copy TargetSheet.Range("A2")

If you still get the Method of Range Object Failed message, then
1. Isolate the exact object by using the actual sheet name instead of the variable first for your source sheet then for your destination sheet.
2. If you can isolate which one is causing the problem, then check your Set statement for spelling differences between the set statement and the copy statement.

I am guessing it is a typo somewhere back up the line that is causing the problem here. Or you simply need to qualify the second Range("C2") with the sheet reference.

SourceSheet.Range("C2", SourceSheet.Range("C2").Offset(SizeOfRange, 0)).Copy TargetSheet.Range("A2")

 

[dhunton]

I have no idea what that Resize function does... but it works... LOL

Thanks for the tip! I'll research it more so I can use it going forward.

 

[JLGWhiz]

I have no idea what that Resize function does... but it works... LOL

Thanks for the tip! I'll research it more so I can use it going forward.

The resize just takes an anchor cell and uses it to shape the range you want to work with (nbr of rows, nbr of columns). Shorter that trying to include all the object references.
Regards, JLG