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