Tab name = what's in a cell
 

Is there a way to get a Tab name to reference what is in a specific cell?
--
Eric

Tab name = what's in a cell
 

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific cell?
--
Eric

Tab name = what's in a cell
 

I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific cell?
--
Eric

Tab name = what's in a cell
 

Absolutely. Rightclick sheet tab, "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
On Error Resume Next
Me.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
End Sub

HTH. Best wishes Harald

"Eric D" wrote in message
...
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how
does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific
cell?
--
Eric

Tab name = what's in a cell
 

the cell i want to reference is on another tab will this formula still work?
--
Eric


"Harald Staff" wrote:

Absolutely. Rightclick sheet tab, "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
On Error Resume Next
Me.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
End Sub

HTH. Best wishes Harald

"Eric D" wrote in message
...
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how
does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific
cell?
--
Eric

Tab name = what's in a cell
 

Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, me.Range("A5")) Is Nothing Then
Exit Sub
end if
On Error Resume Next
Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
On Error Goto 0
End Sub

This looks at A5 of the sheet that owns the code. And it changes the name of
the worksheet that has a code name of Sheet9999.

You can find the codename of the worksheet by opening the VBE (alt-f11).
Showing the project explorer (ctrl-r)
Expanding the workbooks project (like expanding a folder in windows explorer)

Under the Microsoft Objects section, you'll see something like:
Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel)

The codename is the name to the left of the name in ()'s.

You'll have to change that in the code.

And this code goes in the worksheet module for the worksheet that contains the
cell getting changed.

Eric D wrote:

the cell i want to reference is on another tab will this formula still work?
--
Eric

"Harald Staff" wrote:

Absolutely. Rightclick sheet tab, "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
On Error Resume Next
Me.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
End Sub

HTH. Best wishes Harald

"Eric D" wrote in message
...
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how
does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific
cell?
--
Eric





--

Dave Peterson

Tab name = what's in a cell
 

hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will
contain the "A5" i want to reference. Do i just add a module to the workbook
and paste that formula in there? (changing of course the reference of the
sheet i am wanting to change)
--
Eric


"Dave Peterson" wrote:

Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, me.Range("A5")) Is Nothing Then
Exit Sub
end if
On Error Resume Next
Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
On Error Goto 0
End Sub

This looks at A5 of the sheet that owns the code. And it changes the name of
the worksheet that has a code name of Sheet9999.

You can find the codename of the worksheet by opening the VBE (alt-f11).
Showing the project explorer (ctrl-r)
Expanding the workbooks project (like expanding a folder in windows explorer)

Under the Microsoft Objects section, you'll see something like:
Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel)

The codename is the name to the left of the name in ()'s.

You'll have to change that in the code.

And this code goes in the worksheet module for the worksheet that contains the
cell getting changed.

Eric D wrote:

the cell i want to reference is on another tab will this formula still work?
--
Eric

"Harald Staff" wrote:

Absolutely. Rightclick sheet tab, "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
On Error Resume Next
Me.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
End Sub

HTH. Best wishes Harald

"Eric D" wrote in message
...
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how
does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific
cell?
--
Eric





--

Dave Peterson

Tab name = what's in a cell
 

Right click on the worksheet tab that contains that A5 that you'll be changing.

Select View code and you'll be there.

Eric D wrote:

hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will
contain the "A5" i want to reference. Do i just add a module to the workbook
and paste that formula in there? (changing of course the reference of the
sheet i am wanting to change)
--
Eric

"Dave Peterson" wrote:

Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, me.Range("A5")) Is Nothing Then
Exit Sub
end if
On Error Resume Next
Sheet9999.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
On Error Goto 0
End Sub

This looks at A5 of the sheet that owns the code. And it changes the name of
the worksheet that has a code name of Sheet9999.

You can find the codename of the worksheet by opening the VBE (alt-f11).
Showing the project explorer (ctrl-r)
Expanding the workbooks project (like expanding a folder in windows explorer)

Under the Microsoft Objects section, you'll see something like:
Sheet1 (NameYouSeeOnTheWorkSheetTabInExcel)

The codename is the name to the left of the name in ()'s.

You'll have to change that in the code.

And this code goes in the worksheet module for the worksheet that contains the
cell getting changed.

Eric D wrote:

the cell i want to reference is on another tab will this formula still work?
--
Eric

"Harald Staff" wrote:

Absolutely. Rightclick sheet tab, "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
On Error Resume Next
Me.Name = Trim$(Replace(Me.Range("A5").Value, _
"For The Month Ending ", ""))
End Sub

HTH. Best wishes Harald

"Eric D" wrote in message
...
I have a template saved. Every month cell A5 = "For The Month Ending
February 2009" for february and this changes for each month. I just want
the a tab to change based on what was pasted in cell A5. Does that make
sense?
--
Eric


"Harald Staff" wrote:

Yes.

You'll need a macro for that. Changing a sheetname causes changes to all
local and remote formulas that depends on cells in this sheet.

So before you ask "which macro", let me ask which specific cell and how
does
it change its value?

Best wishes Harald

"Eric D" wrote in message
...
Is there a way to get a Tab name to reference what is in a specific
cell?
--
Eric





--

Dave Peterson


--

Dave Peterson

Tab name = what's in a cell
 

Just right-click on the appropriate Sheet tab and "View Code"

Copy/paste Dave's code into that module.

Edit to suit then Alt + q to return to Excel window.

Or if you have VBE open already with your your Microsoft Excel Objects
expanded, just double-click on the Sheet1 or 2 or whichever


Gord Dibben MS Excel MVP

On Thu, 5 Mar 2009 12:14:01 -0800, Eric D
wrote:

hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will
contain the "A5" i want to reference. Do i just add a module to the workbook
and paste that formula in there? (changing of course the reference of the
sheet i am wanting to change)

Tab name = what's in a cell
 

I told you how to do this. You didn't even test ths code I wrote for you,
did you?


"Eric D" wrote in message
...
hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will
contain the "A5" i want to reference. Do i just add a module to the
workbook
and paste that formula in there? (changing of course the reference of the
sheet i am wanting to change)
--
Eric

Tab name = what's in a cell
 

He did change what he wanted, though.


Harald Staff wrote:

I told you how to do this. You didn't even test ths code I wrote for you,
did you?

"Eric D" wrote in message
...
hey dave, thanks for the suggestion, but i am very very visual basic
impared, i know nothing ... i don't see a module for the sheet that will
contain the "A5" i want to reference. Do i just add a module to the
workbook
and paste that formula in there? (changing of course the reference of the
sheet i am wanting to change)
--
Eric

--

Dave Peterson