Tab name = what's in a cell

[Eric D]

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

 

[Harald Staff]

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]

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?

 

[Harald Staff]

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]

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

 

[Dave Peterson]

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.

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

 

[Eric D]

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)

 

[Dave Peterson]

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

Select View code and you'll be there.

 

[Gord Dibben]

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

 

[Harald Staff]

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

 

[Dave Peterson]

He did change what he wanted, though.

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