CODEWITHSUNDEEP
vbaexcel
Margo Hood
Margo Hood

Reputation: 13

How to open a worksheet using its defined variable

I am trying to open a worksheet that is a defined variable. I have a list of ID's and each of those ID's have their own worksheet where the name of the worksheet is the ID number. I am wanting to use the first ID value and assign it to a variable, then open the corresponding worksheet using that variable. Here is the current code I have. The error message is "Object Required".

    Dim proj As Range
    Dim Project As Range

    Set proj = Sheets("Project List").Range("B9")
    Set Project = proj.Value
    Sheets(Project).Activate

Upvotes: 0

Views: 56

Answers (2)

davidhu
davidhu

Reputation: 10472

you just need to convert the range into a string, which is what the argument needs to be for Sheets()

Dim proj As Range

Set proj = Worksheets("Project List").Range("B9")
Sheets(proj.Text).Activate

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169524

You are trying to put a value into an object. Also you should probably specify the workbook. Try instead:

Dim proj As Range
Dim Project As String

Set proj = ActiveWorkbook.Sheets("Project List").Range("B9")
Project = proj.Value
ActiveWorkbook.Sheets(Project).Activate

Upvotes: 3

Related Questions