Rename wrksht tab from cell info w/Macro

  • Thread starter Thread starter bobgerman
  • Start date Start date
B

bobgerman

Can anyone help me with Macro code that will rename a worksheet ta
referencing info found in a cell?

When recording the macro, I pointed to the cell, right clicked an
copied the cell. Then double clicked on the tab to be renamed an
Ctrl-V to paste the copied information. However, the recorded macr
looks like this:

Sub RenameTab()
'
' RenameTab Macro
' Macro recorded 12/18/2003 by Bob G.
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("E2").Select
Selection.Copy
Sheets("TAB").Select
Sheets("TAB").Name = "123456"
End Sub

In the 'Sheets("TAB").Name = "123456" ' line I would like to referenc
the absolute cell on that particular worksheet to get the info for th
new tab (worksheet) name. The "123456" is the info that was in the cel
when I copied it.

Thanks
 
Instead of this
Range("E2").Select
Selection.Copy
Sheets("TAB").Select
Sheets("TAB").Name = "123456"
End Sub

use
Sheets("TAB").Name = [e2]
 
Try this to set the tab name on the first worksheet to the contents of the
cell E2. Change the references to suit your needs. By referencing the sheet
and cells by index values you can programmatically set the values if you
need to.
Note: You cannot have two sheets with the same name so you might want to
check the value of the sheetname before you try to set it to one that
already exists.

Sub RenameTab()
With Worksheets(1)
' set the cell content to name the sheet (tab) by row,col
.Name = .Cells(2, 5)
End With
EndSub

Cheers
Nigel
 
Back
Top