excel vba: sheet name as variable

Question

I am trying to select a sheet based on the variable. I could successfully rename the sheet with variable sname but could not activate the same

Dim sname as String
sname = Trim(Cells(row, 12).Value)
Sheets("Blank (2)").name = sname
Sheets(sname).Activate
Sheets(sname).Select

Why would you need to .Activate and .Select the sheet anyway? — Mathieu Guindon
– Mathieu Guindon, Commented Nov 7, 2016 at 3:00
subscript out of range in this case would mean the sheet doesn't exist. sure it's right? sure you've got the right workbook open? — CustodianOfCode
– CustodianOfCode, Commented Nov 7, 2016 at 5:14

Community · Accepted Answer · 2017-05-23 11:53:35Z

2

You're saying this works?

Sheets("Blank (2)").name = sname

Then, do this:

With Sheets("Blank (2)")
    .Name = sname
    .Activate
    .Select
End With

The idea is to grab a reference to the worksheet you're working with, instead of constantly fetching the object from the Sheets collection every time.

Alternatively:

Dim target As Worksheet
Set target = ThisWorkbook.Worksheets("Blank (2)")
target.Name = sname
target.Activate
target.Select

Note that the Sheets collection, unqualified as you've used it, implicitly refers to the active workbook. Assuming that active workbook is the same workbook the code is written in, it's best to qualify the call with ThisWorkbook, and to use the Worksheets collection instead - because the Sheets collection can contain non-worksheet objects (e.g. charts).

Lastly, I doubt there's any need to .Select anything here. Read more about avoiding .Select and .Activate here.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Nov 7, 2016 at 3:08

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

excel vba: sheet name as variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related