CODEWITHSUNDEEP
vba
Dinesh M
Dinesh M

Reputation: 39

excel vba: sheet name as variable

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

Upvotes: 0

Views: 39908

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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.

Upvotes: 2

Related Questions