Excel paste error

[Wolfen02]

For some reason I keep getting one of those annoying "Runtime 1004" errors when attempting to use the following copy/paste method. Still learning and can't seem to figure out where I've gone astray.

where r = last row of data on sheet1:

    For m = 1 To k
        If WB.Worksheets("Data").Cells(m, 1).Value = "" Then
            WB.Worksheets("Data").Range(m & "2:" & m & "10").Copy
            r = r + 1
            WB.Worksheets(1).Range(r & "3").PasteSpecial Paste:=xlValues
            WB.Worksheets(1).Cells(r, 1).Formula = "Y"
        End If
    Next m

Heading home for now. Thanks for any help!

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[PHV]

Range(m & "2:" & m & "10")
Looks like an illegal range definition.
Try to play with the Cells collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SonicBoomBand]

Range(m & "2:" & m & "10")

The Range Definition is incorrect. You have assigned 'm' (your column ref) to an integer, but the range option will only allow a string for a column ie. A, B, C.

Regards
Andrew

Andrew Chamberlain
National Grid Transco

 

[TonyJollans]

Hi Wolfen02,

.Range(m & "2:" & m & "10") is NOT an invalid Range, but is probably not what you want. The reason for your error is probably that the paste range is a different shape from the copy range.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Wolfen02]

Thanks for all your help! Andrew, you were absolutely correct. I had to switch my range reference around. I was referring to my row first as you would in a cell reference. Oh well, I'd definately been staring at the screen too long and overlooking that one! Works like a charm now.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions