How can I use a string from a text box as a reference for a range in excel vba?

[Elfman]

Hi! I'm quite a newbie to VBA macros and I am in urgent need. I created a Userform to save some time with copy and pasting excel content. Now I have the following issue: I get a string output from a Userform - Textbox (txtHRS_ANW). I created exactly the same names as named ranges. Now I want to use the string output as a reference for the named range. I am not able to add the double quotes in the brackets - i tried using """" and chr(34) My code is as follows:

 Private Sub cmdHRSLoading_Click()
    Dim NameRange As String
    If chkANW = True Then
        NameRange = Me.txtHRS_ANW.Value 'gives me following string HRS_ANW_CORP01 (until CORP10 depending on user entry)
        Sheet5.Range(NameRange).Select 'here I would like to use the string as a reference for the range
        Selection.Copy
        Sheet9.Range("A" & Rows.count).End(xlUp).Offset(1).Select
        Selection.Paste
    End If
    
    End Sub

 

[baitmaster]

Welcome to the board

I'm assuming the text string you get IS the correct range name, and that there is only one of each in the workbook (i.e. not duplicated on move/copied worksheets). You therefore don't need to reference the worksheet (range name is enough, so long as you aren't working in a separate workbook), and you don't need to select anything (I'll let you off that one as you're a VBA novice, but it's a bad habit!). It also looks like you're working on Sheet9 and trying to select on Sheet5 without first selecting Sheet5, which is probably the source of your problem. If instead you're on Sheet5, then your code to select the range on Sheet9 would give the wrong result as "Rows.Count" will default to the currently-active worksheet, as the Row object is not fully referenced. Finally I've renamed your variable to clarify that it's a string - as you write longer and longer macros, you'll need to easily see what type of variable you're working with, so get in the habit of using a specific convention, e.g. str###, int###, dbl### etc.

Try this instead

Private Sub cmdHRSLoading_Click()
Dim strNameRange As String

If chkANW = True Then
    strNameRange = Me.txtHRS_ANW.Value
    Range(strNameRange).Copy
    Sheet9.Range("A" & Sheet9.Rows.count).End(xlUp).Offset(1,0).Paste
End If

End Sub