Simple VBA question

[acsmiamiguy]

Need to jump to sheets based on a range name
Item = Range("B2")
MsgBox (Item)
Sheets(Item).Select

The MsgBox code works and gives the result of '100', which is the contents of cell B2. I thought if that is true, then the next line Sheets(Item).Select would work, since 'Item' is equal to '100)'. Only it doesn't. Very frustrating.
What am I missing?
Thanks!

 

[JoeMo]

Since you are using Item as a sheet index number, this will work only if Range("B2") holds the number 100 and you have at least 100 sheets in your workbook. If B2 has a text value "100". Your code will incur a run time error. Ditto if there are not at least 100 sheets present.

 

[Jonmo1]

If Range("B2") contains a 100 in the TEXT format (you said the message box returns '100')
Then the sheets(Item).Select is looking for a sheet literally named 100 (not the 100th sheet by index number)

Try

Item = Val(Range("B2"))

 

[acsmiamiguy]

So is it possible to pass a cell value to name a sheet?

 

[Joe4]

Are you looking to select the Index (100th sheet), or is there a sheet named "100" that you are trying to select.
If Index, you must have at least 100 sheets.
If sheet named "100", declare Item to be a String first, i.e.

Dim Item As String
Item = Range("B2")
MsgBox (Item)
Sheets(Item).Select

 

[Fluff]

Bearen 2it

 

[acsmiamiguy]

Thanks. Will try out soon.

 

[JoeMo]

So is it possible to pass a cell value to name a sheet?

Yes, but the code you posted is selecting a sheet not naming a newly added sheet or changing the name of an existing sheet.