Using Cell Contents to Select Worksheet with Same Name as Cell Con

I am a quantitative analyst who is the leader of our department's Excel users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department, but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the contents
of a cell in the workbook, and use it to select a worksheet (in that same
workbook) that has the same name as the contents or value of that cell. This
seems to be a "twist" on the topic of automated worksheet names that has
generated several postings. Any help will be greatly appreciated by both of
us. Thanks for any help that can be provided!

Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in that
same
workbook) that has the same name as the contents or value of that cell.
This
seems to be a "twist" on the topic of automated worksheet names that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

hiya

two comments
1) have a look at and a play with the INDIRECT formula from the excel help.
(you can have in Cell A1 a text saying " sheet2!A4 ".
And if you use =INDIRECT(A1) excel will return the value from sheet 2 cell A4.
I think this might be what you mean.

2) If you really mean activating sheets, it needs to be done with code like
this.

Sub allllen()
Sheets(Range("B2").Value).Activate
End Sub

--
Allllen


"Alan Sexter" wrote:

I am a quantitative analyst who is the leader of our department's Excel users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department, but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the contents
of a cell in the workbook, and use it to select a worksheet (in that same
workbook) that has the same name as the contents or value of that cell. This
seems to be a "twist" on the topic of automated worksheet names that has
generated several postings. Any help will be greatly appreciated by both of
us. Thanks for any help that can be provided!

hiya

two comments
1) have a look at and a play with the INDIRECT formula from the excel help.
(you can have in Cell A1 a text saying " sheet2!A4 ".
And if you use =INDIRECT(A1) excel will return the value from sheet 2 cell A4.
I think this might be what you mean.

2) If you really mean activating sheets, it needs to be done with code like
this.

Sub allllen()
Sheets(Range("B2").Value).Activate
End Sub

--
Allllen
--
Allllen


"Alan Sexter" wrote:

I am a quantitative analyst who is the leader of our department's Excel users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department, but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the contents
of a cell in the workbook, and use it to select a worksheet (in that same
workbook) that has the same name as the contents or value of that cell. This
seems to be a "twist" on the topic of automated worksheet names that has
generated several postings. Any help will be greatly appreciated by both of
us. Thanks for any help that can be provided!

Simon Lloyd,

I just talked with my friend from Chicago, and we would both prefer VBA.
Thanks for responding - I look forward to your upcoming response!

Alan Sexter

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in that
same
workbook) that has the same name as the contents or value of that cell.
This
seems to be a "twist" on the topic of automated worksheet names that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

Hi,

1. Let's suppose that cell A1 of Sheet2 contain the myData, then in cell A1
of Sheet1 enter the formula =Sheet2!A1. This cell will now display "myData".
Now select cell A1 on Sheet1 and press Ctrl+[ This will take you to
Sheet2!A1. No VBA necessary.

2. If you like VBA here are two solutions. Name the range A1 on Sheet2
Data (just to be different) - Insert, Name, Define. In cell A1 of Sheet1
enter the text Data. Now add the following macro to the Sheet1 object:
A]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
If Not Intersect(Target, [A1]) Is Nothing Then
Application.Goto Reference:=Target.Value
End If
End Sub

In this case you are moved to the named range when you double click the name
in cell A1. You can use many other events to trigger the macro.

B]

Sub myMove()
Application.Goto Reference:=Sheet1.[A1].Value
End Sub

This macro goes into a regular VBA module. Assign a shortcut key to this
macro and it can be executed fro any location within the workbook.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Alan Sexter" wrote:

Simon Lloyd,

I just talked with my friend from Chicago, and we would both prefer VBA.
Thanks for responding - I look forward to your upcoming response!

Alan Sexter

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in that
same
workbook) that has the same name as the contents or value of that cell.
This
seems to be a "twist" on the topic of automated worksheet names that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

Hi Simon,
I am the former co-worker of Al with the question. I reviewed your answers.
The 1st answer that is not using VBA maybe be misunderstood. I want to use
the contents of a cell i.e, 200904 to be used to find the worksheet name
200904 to be selected. Then, copy this '200904' sheet and rename this copied
worksheet using another cell's contents, i.e., 200905.
Thanks,
Susan

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in that
same
workbook) that has the same name as the contents or value of that cell.
This
seems to be a "twist" on the topic of automated worksheet names that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

This message should be directed to Shane. Susan needs to use VBA in order for
this function (or set of tasks) to be run in a macro. Thanks!

Alan Sexter

"Susan Schmid" wrote:

Hi Simon,
I am the former co-worker of Al with the question. I reviewed your answers.
The 1st answer that is not using VBA maybe be misunderstood. I want to use
the contents of a cell i.e, 200904 to be used to find the worksheet name
200904 to be selected. Then, copy this '200904' sheet and rename this copied
worksheet using another cell's contents, i.e., 200905.
Thanks,
Susan

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau,
Wisconsin). I have a friend who is a former co-worker in my department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in that
same
workbook) that has the same name as the contents or value of that cell.
This
seems to be a "twist" on the topic of automated worksheet names that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

This code goes in the worksheet code module that you want to click the
cells in, i have commented the code so you understand it, it can
probably be done a lot smarter but this works and you can understand it:


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Sh As Worksheet
'don't show changes until operation complete
Application.ScreenUpdating = False
'if more than one cell selected exit sub
If Target.Cells.Count 1 Then Exit Sub
'if we are not clicking in column 1 exit sub
If Target.Column 1 Then Exit Sub
'On Error GoTo Nxt
'to leave the copy in same workbook remove ' before "After"
Sheets(Target.Value).Copy After:=Sheets(Sheets.Count)
'check if sheet exists
For Each Sh In Sheets
If Sh.Name = ActiveSheet.Range("A1").Value Then
'don't show alerts for deletion
Application.DisplayAlerts = False
ActiveSheet.Delete
'turn alerts back on
Application.DisplayAlerts = True
GoTo Nxt1
End If
Next Sh
'rename the copied sheet to the contents of A1 of the new sheet
ActiveSheet.Name = ActiveSheet.Range("A1").Value
GoTo SubEnd
Nxt1:
MsgBox "Sheet already exists, it will not be created!"
GoTo SubEnd
Nxt:
MsgBox "No sheet found of that name"
SubEnd:
'show changes
Application.ScreenUpdating = False
'back to original sheet
Me.Activate
End Sub

--------------------


*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*

Susan Schmid;381016 Wrote:
Hi Simon,
I am the former co-worker of Al with the question. I reviewed your
answers.
The 1st answer that is not using VBA maybe be misunderstood. I want to
use
the contents of a cell i.e, 200904 to be used to find the worksheet
name
200904 to be selected. Then, copy this '200904' sheet and rename this
copied
worksheet using another cell's contents, i.e., 200905.
Thanks,
Susan

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's
Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in
Wausau,
Wisconsin). I have a friend who is a former co-worker in my
department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in
that
same
workbook) that has the same name as the contents or value of that
cell.
This
seems to be a "twist" on the topic of automated worksheet names
that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a
static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('http://www.thecodecage.com'
(http://www.thecodecage.com/))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Using Cell Contents to Select Worksheet with Same
Name as Cell Con - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=106281)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281

Hi Simon,
The comments are helpful. Perhaps, I didn't describe the situation as
thoroughly enough.
I created the following macro at work to activate by pressing Ctrl+A but I
don't want it to be the same value(s) of 200904 (or 200905) because I am
dealing with monthly reports. I also don't want to manually change it
everytime. So, I created an additional spreadsheet named "Start" where cell
B3 is the current monthly pull of data 200905 and cell B4 is the previous
monthly pull of data 200904. Once, the previous month's pull of data
worksheet is copied and renamed to the current month's pull of data. It is
ready for me to update the spreadsheet with the new info. Each month the
corresponding worksheet names will change.
Right now, I am in a tough position trying to refer to the contents of these
cells in this macro. Any thoughts?

' CopyNewMonth Macro
' Macro recorded 5/12/2009 by sschmid to copy the worksheet for the current
latest month (previous month's pull) into a new worksheet for the month to be
added (current month's pull).
' Modified 5/17/2009 by sschmid in order to change Sheets(17)to
'Sheets("Benchmark")
'
' Keyboard Shortcut: Ctrl+a
' Update month

Sheets("200904").Copy Befo=Sheets("Benchmark")

'Already selected
'Sheets("200904 (2)").Select

Sheets("200904 (2)").Name = "200905"
End Sub

"Simon Lloyd" wrote:


This code goes in the worksheet code module that you want to click the
cells in, i have commented the code so you understand it, it can
probably be done a lot smarter but this works and you can understand it:


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Sh As Worksheet
'don't show changes until operation complete
Application.ScreenUpdating = False
'if more than one cell selected exit sub
If Target.Cells.Count 1 Then Exit Sub
'if we are not clicking in column 1 exit sub
If Target.Column 1 Then Exit Sub
'On Error GoTo Nxt
'to leave the copy in same workbook remove ' before "After"
Sheets(Target.Value).Copy After:=Sheets(Sheets.Count)
'check if sheet exists
For Each Sh In Sheets
If Sh.Name = ActiveSheet.Range("A1").Value Then
'don't show alerts for deletion
Application.DisplayAlerts = False
ActiveSheet.Delete
'turn alerts back on
Application.DisplayAlerts = True
GoTo Nxt1
End If
Next Sh
'rename the copied sheet to the contents of A1 of the new sheet
ActiveSheet.Name = ActiveSheet.Range("A1").Value
GoTo SubEnd
Nxt1:
MsgBox "Sheet already exists, it will not be created!"
GoTo SubEnd
Nxt:
MsgBox "No sheet found of that name"
SubEnd:
'show changes
Application.ScreenUpdating = False
'back to original sheet
Me.Activate
End Sub

--------------------


*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*

Susan Schmid;381016 Wrote:
Hi Simon,
I am the former co-worker of Al with the question. I reviewed your
answers.
The 1st answer that is not using VBA maybe be misunderstood. I want to
use
the contents of a cell i.e, 200904 to be used to find the worksheet
name
200904 to be selected. Then, copy this '200904' sheet and rename this
copied
worksheet using another cell's contents, i.e., 200905.
Thanks,
Susan

"Simon Lloyd" wrote:


Alan Sexter;379826 Wrote:
I am a quantitative analyst who is the leader of our department's
Excel
users
group (Multi-Channel Marketing department of Eastbay, Inc. in
Wausau,
Wisconsin). I have a friend who is a former co-worker in my
department,
but
is now a senior credit analyst with a company in Chicago.

If possible, this credit analyst would like to have Excel take the
contents
of a cell in the workbook, and use it to select a worksheet (in
that
same
workbook) that has the same name as the contents or value of that
cell.
This
seems to be a "twist" on the topic of automated worksheet names
that
has
generated several postings. Any help will be greatly appreciated by
both of
us. Thanks for any help that can be provided!If it is going to be a
static name then it can be a hyperlink, otherwise
it would have to be done with VBA........which would you prefer?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('http://www.thecodecage.com'
(http://www.thecodecage.com/))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Using Cell Contents to Select Worksheet with Same
Name as Cell Con - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=106281)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281