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