Need help in Excel - GM Forum - Buick, Cadillac, Chev, Olds, GMC & Pontiac chat


Lounge For casual talk about things unrelated to General Motors. In other words, off-topic stuff. And anything else that does not fit Section Description.

Reply
 
 
 
Thread Tools Search this Thread
Old 10-27-2006, 07:43 PM   #1
Senior Member
Certified GM nut
Thread Starter
 
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Thanks: 0
Thanked 0 Times in 0 Posts
Technical Ted is on a distinguished road
Default 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?
Technical Ted is offline   Reply With Quote
Old 10-27-2006, 07:47 PM   #2
Senior Member
Posts like a Camaro
 
Join Date: Nov 2002
Posts: 1,109
Thanks: 0
Thanked 0 Times in 0 Posts
1995BvSSE is on a distinguished road
Default Re: Need help in Excel

Quote:
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?
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.
1995BvSSE is offline   Reply With Quote
Old 10-27-2006, 08:36 PM   #3
Senior Member
Certified GM nut
Thread Starter
 
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Thanks: 0
Thanked 0 Times in 0 Posts
Technical Ted is on a distinguished road
Default

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!
Technical Ted is offline   Reply With Quote
Old 10-27-2006, 08:52 PM   #4
Senior Member
Posts like a Camaro
 
Join Date: Nov 2002
Posts: 1,109
Thanks: 0
Thanked 0 Times in 0 Posts
1995BvSSE is on a distinguished road
Default

Quote:
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!
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.
1995BvSSE is offline   Reply With Quote
Old 10-28-2006, 06:09 PM   #5
PopaDopaDo
True Car Nut
 
popatim's Avatar
 
Join Date: Jan 2005
Location: NY
Posts: 4,957
Thanks: 0
Thanked 0 Times in 0 Posts
popatim is on a distinguished road
Default

Are these sheets all in the same workbook?
popatim is offline   Reply With Quote
Old 10-28-2006, 07:06 PM   #6
PopaDopaDo
True Car Nut
 
popatim's Avatar
 
Join Date: Jan 2005
Location: NY
Posts: 4,957
Thanks: 0
Thanked 0 Times in 0 Posts
popatim is on a distinguished road
Default

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.
popatim is offline   Reply With Quote
Old 10-28-2006, 07:55 PM   #7
Senior Member
Certified GM nut
Thread Starter
 
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Thanks: 0
Thanked 0 Times in 0 Posts
Technical Ted is on a distinguished road
Default

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
Technical Ted is offline   Reply With Quote
Old 10-29-2006, 09:58 AM   #8
Senior Member
True Car Nut
 
Join Date: Feb 2006
Location: Melrose
Posts: 4,596
Thanks: 0
Thanked 0 Times in 0 Posts
BLACK94SSEi is on a distinguished road
Default

Wheres Boosty? He was telling me hes an Excel Pro.
BLACK94SSEi is offline   Reply With Quote
Old 10-29-2006, 10:02 PM   #9
Senior Member
Certified GM nut
Thread Starter
 
Join Date: Oct 2005
Location: Western New York
Posts: 1,942
Thanks: 0
Thanked 0 Times in 0 Posts
Technical Ted is on a distinguished road
Default

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.
Technical Ted is offline   Reply With Quote
Old 10-29-2006, 10:07 PM   #10
PopaDopaDo
True Car Nut
 
popatim's Avatar
 
Join Date: Jan 2005
Location: NY
Posts: 4,957
Thanks: 0
Thanked 0 Times in 0 Posts
popatim is on a distinguished road
Default

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
popatim is offline   Reply With Quote
 
 
Reply

Related Topics
Thread Thread Starter Forum Replies Last Post
Need Need Help With A Few Codes Kamikaze78 Chevrolet 4 01-31-2015 10:37 AM
quick excel question jwakamud Lounge 4 01-30-2008 08:03 PM
Need help replacing Hub assembly just need socket sizes. 19bonnie95 1992-1999 6 05-18-2007 02:40 PM
Need help, 2000 bonne trans codes.. need answer!!! caminated2k Lounge 3 02-09-2005 10:59 PM
need help asap.. need to keep powered when removing battery Custom88 Everything Electrical & Electronic 10 06-22-2004 11:58 PM


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT -4. The time now is 02:08 PM.


We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.