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

Question

I'm quite a newbie to VBA macros. 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

do you get any error? what is its number? try to activate your sheet first with Sheet5.activate and next call .select line. Please remember that you don't need to select to trigger .copy >> .paste methods — Kazimierz Jawor
– Kazimierz Jawor, Commented Dec 8, 2016 at 8:09

Harun24hr · Accepted Answer · 2016-12-08 08:19:12Z

1

Use the following procedure:

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

Use Application.GoTo method. Also you do not need to use .value after Me.txtHRS_ANW

--- Use ActiveSheet.Paste not Selection.Paste

edited Dec 8, 2016 at 8:19

answered Dec 8, 2016 at 8:11

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr. Elfman Over a year ago

Hello Harun - Thanks for the fast and accurate answer! You saved my day - Spot on!

SierraOscar Over a year ago

It might be worth explaining why you don't need to use .Value for other people that come to view this post in the future, and possibly the OP.

SierraOscar · Accepted Answer · 2016-12-08 08:54:35Z

1

Can also be written as:

Private Sub cmdHRSLoading_Click()

    If chkANW Then
        With Sheet9
            With .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1)
                .Resize(Range(Me.txtHRS_ANW.Text).Rows.Count, Range(Me.txtHRS_ANW.Text).Columns.Count).Value = Range(Me.txtHRS_ANW.Text).Value
            End With
        End With
    End If

End Sub

Negating the need to use the clipboard.

answered Dec 8, 2016 at 8:54

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

2 Comments

Mr. Elfman Over a year ago

Thank you! Just one more question - I wrote the macro in Excel 2016 - How can I make it work in Excel 2010?

SierraOscar Over a year ago

There should be no difference in the code really - I'd suggest posting a new question of something isn't working on a specific version

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related