Need help in Excel
#1
Senior Member
Certified GM nut
Thread Starter
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Likes: 0
Received 0 Likes
on
0 Posts
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?
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?
#2
Senior Member
Posts like a Camaro
Join Date: Nov 2002
Posts: 1,109
Likes: 0
Received 0 Likes
on
0 Posts
Re: Need help in Excel
Originally Posted by Technical Ted
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?
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?
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.
#3
Senior Member
Certified GM nut
Thread Starter
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Likes: 0
Received 0 Likes
on
0 Posts
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'* probably something very easy!
Sheets("B2").Select - Didn't work
Also tried Formatting cell B2 as Text & also as Number - didn't help.
It'* probably something very easy!
#4
Senior Member
Posts like a Camaro
Join Date: Nov 2002
Posts: 1,109
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Technical Ted
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'* probably something very easy!
Sheets("B2").Select - Didn't work
Also tried Formatting cell B2 as Text & also as Number - didn't help.
It'* probably something very easy!
As for why the first one didn't work... I'm not really sure. Might be a VB limitation.
#6
PopaDopaDo
True Car Nut
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.
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.
#7
Senior Member
Certified GM nut
Thread Starter
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Likes: 0
Received 0 Likes
on
0 Posts
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
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
#9
Senior Member
Certified GM nut
Thread Starter
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Likes: 0
Received 0 Likes
on
0 Posts
Here'* 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.
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.