CODEWITHSUNDEEP
excelvba
Majid
Majid

Reputation: 87

Naming a sheet from a variable

I am trying use a variable name to define the name of a sheet. The code throws up an error at "Find" in the below code. 

  Dim z As String
    Dim r As String
    z = ActiveWorkbook.Name

    r = Mid(z, Find("_", z, 1), 30)
    ActiveSheet.Name = r

Upvotes: 1

Views: 65

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

r = Mid(z, InStr(1, z, "_") + 1, 30)

Upvotes: 2

Storax
Storax

Reputation: 12167

The solution with the worksheet function Find would be

r = Mid(z, WorksheetFunction.Find("_", z, 1) + 1, 30)

Upvotes: 3

basodre
basodre

Reputation: 5770

It seems that you want the value in r to contain whatever was in z after an underscore. If so, try

r = mid(z, instr(1, z, "_") + 1)

Upvotes: 4

Related Questions