Need help in Excel
 

I'm trying to write a macro & need to select data on different worksheets. The 10 worksheets have been named with the numbers 0-9. If I record a macro this is the line when worksheet 5 is manually selected.

Sheets("5").Select

What I want to do is use the value thats in cell B2 of the active worksheet to determine which worksheet is selected. Something like this.

Sheets(B2).Select

Anyone know the correct way to do this?

Re: Need help in Excel
 

Sounds like you need to convert an integer to a string

This may help.

http://msdn.microsoft.com/library/de...6cfd0a3fe1.asp

EDIT: CStr looks to be the right function. So something like Sheets(CStr(B2)).Select should do it.

Sheets(CStr(B2)).Select - Didn't work
Sheets("B2").Select - Didn't work

Also tried Formatting cell B2 as Text & also as Number - didn't help.
It's probably something very easy!

Wouldn't expect the latter to work since you are treating B2 as a literal string.

As for why the first one didn't work... I'm not really sure. Might be a VB limitation.

Are these sheets all in the same workbook?

Not 100% sure what you're are trying to do.

in the macro its
Sheets("Sheet5").Select
Range("B2").Select

to select a cell. This will physicall select cell B2 on sheet 5 just as if you clicked on it with your mouse. it will not return a value which is what I think you want.

in a cell formula its: sheet5!B2
theres an exclaimation after the 5 in case its hard to read.

The sheets are all in the same Workbook.

The sheets have all been renamed. 'Sheet1' is now '1'. 'Sheet2' is now '2'. Etc.

I want to select the worksheet that is called out in cell B2 of the active worksheet. Confusing Eh? Examples:
If Cell B2 = 1 then I want to select sheet '1'.
If Cell B2 = 5 then I want to select sheet '5'.

Something like this.
Sheets("value of B2").Select

Wheres Boosty? He was telling me hes an Excel Pro. ;)

Here's what I needed.

Dim x As String
x = Sheets("Main").Range("B2").Value
Sheets(x).Select

Someone at techsupportforums knew the answer. It took someone knowledgeable in Visual Basic more than Excel.

Thanks to everyone that gave it a shot.

Heres the long way:

Sub macro1()
Set b2 = Range("B2")
If b2 = 1 Then Sheet1.Activate
If b2 = 2 Then Sheet2.Activate
If b2 = 3 Then Sheet3.Activate
End Sub