Refer to named range using variables

[gmcgough600]

Hi,

I'm trying to copy a named range without having to have the name of the range hard coded but having difficulty getting this to work, attempt below but it's not working as I expected it to:

This is at the top of the Module as I'm using a UserForm to define the variable site_name:

Public site_name As String

site_name has been set to "PEHE"

Application.Range(site_name & "_PI_tags").Copy

The named range is PEHE_PI_tags so I had assumed that I can construct this name from a combo of var and the text string "_PI_tags".

Thanks

 

[Fluff]

Is all the code in the userform module?

 

[steve the fish]

You can construct the name from the variable and the string so id guess you either misspelled the name or the variable doesnt hold the value you think it does.

 

[gmcgough600]

No none of the code I've given is, the only code used there is:

Private Sub UserForm_Initialize()'Link OptionButton names to site name cells
Me.OptionButton1.Caption = ThisWorkbook.Sheets("Input").Range("C5").Value
Me.OptionButton2.Caption = ThisWorkbook.Sheets("Input").Range("D5").Value
End Sub

Range C5 contains "PEHE"

Private Sub OKCommandButton_Click()
   Select Case True
      Case Me.OptionButton1
         site_name = ThisWorkbook.Sheets("Input").Range("C5")
      Case Me.OptionButton2
         site_name = ThisWorkbook.Sheets("Input").Range("D5")
   End Select
Unload Me
End Sub

 

[gmcgough600]

Thanks, the named range is definitely "PEHE_PI_tags" and watch window shows:

Expression Value Type
site_name : "PEHE" : String :

You can construct the name from the variable and the string so id guess you either misspelled the name or the variable doesnt hold the value you think it does.

 

[Fluff]

In that case try using a message box like

MsgBox site_name
Range(site_name & "_PI_tags").Copy

to check the value

 

[gmcgough600]

Thanks, the MsgBox just says PEHE

 

[gmcgough600]

Working now, thanks all. It may have been working the whole time , sorry for the trouble!

 

[Fluff]

Glad it's sorted & thanks for the feedback