Paste a Range from a variable

[Al]

I keep getting Runtime error 9 - Subscript out of range
when my code hits the line:
rngCopy = Sheets("CTab").Range("CRange") in the code
below. The next line of code may generate the same error
because it contains similar syntax... it just hasn't made
it that far.
(I am populating variables with the worksheet and range
from one worksheet to copy the values held within that
range to a cell on another worksheet when the user clicks
on a command button. All is working fine except for the
persistent error message.) Using Excel 2000.

Dim rngCopy As Range
Dim rngPaste As Range
Dim CTab As Variant
Dim CRange As Variant
Dim PTab As Variant
Dim PRange As Variant
CTab = Sheets("Logic Questions").Range("A53").Value
CRange = Sheets("Logic Questions").Range("H53").Value
PTab = Sheets("Logic Questions").Range("A54").Value
PRange = Sheets("Logic Questions").Range("H54").Value
rngCopy = Sheets(CTab).Range(CRange)
rngPaste = Sheets(PTab).Range(PRange)
rngCopy.Copy
Sheets("Bill").Activate

Thank you in advance for any help you can provide,
Al

 

[Bernie Deitrick]

Al,

Since rngCopy is declared as a range, simple change

rngCopy = Sheets("CTab").Range("CRange")

to

Set rngCopy = Sheets("CTab").Range("CRange")

But, you could simple change

rngCopy = Sheets(CTab).Range(CRange)
rngPaste = Sheets(PTab).Range(PRange)

to

Sheets(CTab).Range(CRange).Copy Sheets(PTab).Range(PRange)

if you are copy/pasting rngeCopy to rngPaste.

HTH,
Bernie
MS Excel MVP

 

[steve]

Al,

Subscript Out-of-Range usually means that Excel doesn't recognize your
references. Or your references aren't there.

As CTab and CRange are variables - remove the quotes
rngCopy = Sheets("CTab").Range("CRange")
s/b
rngCopy = Sheets(CTab).Range(CRange)
(though there aren't quotes in the code)

Also - CTab and CRange (as used in the above code) should be Text.
Check these 2 lines and see what they come up with

CTab = Sheets("Logic Questions").Range("A53").Value
Msgbox CTab
CRange = Sheets("Logic Questions").Range("H53").Value
Msgbox CTab & " " CRange
should give you an indication

You may want to change the '.Value' to '.Text'

 

[Bernie Deitrick]

I shouldn't have copied the code with the quotes from your message
text, but the code without the quotes from your copied code. But the
message is the same: you need to use "Set " when working with range
variables.

HTH,
Bernie
MS Excel MVP