VBA- Refer to a dynamic named range

[Analyst77]

Hi All,

I currently have a dynamic named range that i created using the Name Manager in excel. My question is, can i refer to that named range in VBA for a macro i'm creating? And if so, how do i do that? If i am not able to use the named range, how do i create a dynamic range in VBA then. Currently my named range refers to :

=OFFSET(Overview!$C$2,0,0,COUNTA(Overview!$C$2:$C$3776),1)

I've checked for ways to do this but nothing that seemed very straight forward. Any help would be greatly appreciated.

Thank you!

 

[Norie]

You can refer to a dynamically named range in VBA just as you can any named range.

Are you having problems doing so?

 

[Analyst77]

Yes, I may be doing it wrong though. I'm pretty new to VBA. I thought I would use the range().select command but perhaps not. When i try to run it, i get a "Range class failed" message. This is what i have written below. "Overviewids" is my named range.

Range("Overviewids").Select
Selection.Copy
Sheets("IDs VBA").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Thanks!!

 

[Norie]

You don't to select but you might need a worksheet reference, try this.

Sheets("Overview").Range("Overviewids").Copy

Sheets("IDs VBA").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[Analyst77]

YES!!! That worked. Thank you so much Norie!!!