Selecting a variable bounded range

[rdwinslow]

Hi. I'm going bald on this one! I need to select a range bounded by the values in cells.

I have a choice:

I can select based on values CopyRow1, CopyCo1l to CopyRow2, CopyCol2 where I can calculate the second set as an offset of the first, which is variable - these being integers. ie CopyRow1 = 3, CopyCol1 = 2, CopyRow2 = 20, CopyCol2 = 10 (Just possible values)

or

I can select based on values contained in the variables CopyBox1 and CopyBox2 which are strings - ie CopyBox1 = B3, CopyBox2 = J20

I can easily get these correct values, but every permutation of Range and cells I have tried (including making another variable named CopyRange which is Dim'd as Range and trying Range(CopyRange).Select where CopyRange is "B3:J20" - or any other range depending of the calculates start and end

I have little hair left, and I can't afford to lose much more.

Somebody please help me!

Thanks
Rob

 

[rdwinslow]

Please.....

 

[RoryA]

Not entirely sure what you tried but either:

Range(Cells(Copyrow1, copycol1), cells(copyrow2, copycol2))

or

Range(copybox1, copybox2)

should work (qualified by sheet as required)

 

[rdwinslow]

Not entirely sure what you tried but either:

Range(Cells(Copyrow1, copycol1), cells(copyrow2, copycol2))

or

Range(copybox1, copybox2)

should work (qualified by sheet as required)

both elicit a

Run-time error '1004': Select method of Range class failed

Any ideas? Should I NOT be using a .Select after them? I need to copy the values of the cells into another worksheet.

Thanks for the help!
Rob

 

[RoryA]

There is almost never a need to select ranges in code, but I can't really assist you with code unless I can actually see what you are using. (I suspect your error is that you haven't specified the sheet)

 

[rdwinslow]

I found my solution - rather than selecting the range then copying, I just set it to

Range(CopyBox1, CopyBox2).Copy

.Select was not the answer....

Sigh... I lost a lot of good hair over that!

Thanks for helping me work through it!
Rob