How to open a worksheet using its defined variable

Question

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

mechanical_meat · Accepted Answer · 2016-07-08 16:34:33Z

3

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

answered Jul 8, 2016 at 16:34

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

Comments

davidhu · Accepted Answer · 2016-07-08 16:39:22Z

0

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

answered Jul 8, 2016 at 16:39

davidhu

10.6k7 gold badges37 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to open a worksheet using its defined variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related