Tab naming

[jtcurran96]

Is it possible to have a tab named by referencing a cell within a worksheet?

 

[VoG]

Right click the sheet tab and select View Code. Paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    On Error Resume Next
        Me.Name = Target.Value
    On Error GoTo 0
End If
End Sub

Then close the code window. What you enter in A1 will become the sheet's name (provided that it is a 'legal' sheet name).

 

[jtcurran96]

Thank you.

 

[MistyLogic]

What if the sheet does not exist before the macro runs? i.e. the macro first creates a sheet, data is pasted onto it and the tab name is based on one of the cell values.

 

[VoG]

Do you mean something like

Sub test()
Dim ws As Worksheet
Set ws = Worksheets.Add
Sheets("Sheet2").Range("A1:C10").Copy Destination:=ws.Range("A1")
Application.CutCopyMode = False
On Error Resume Next
ws.Name = ws.Range("A1").Value
On Error GoTo 0
End Sub

 

[MistyLogic]

Well, yes and no. I already have the code set for adding the new sheet and pasting the data. The only part I cannot get to work is the naming of the tab. How do I make it name the tab what the value is on cell K2 on the active sheet?

 

[sous2817]

Can you post the code that you have?

 

[MistyLogic]

Sub inputit()
'
' inputit Macro
' Macro recorded 7/12/2008 by IT
' Modified on 9/4/2008 by ML
'
'
    Sheets("FrontPage").Select
    Sheets.Add
    Range("B2").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False
'
'
' Change location text color and alignment
'
'
    Range("B10").Select
    Selection.Font.ColorIndex = 0
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
' Delete all the picures
'
Dim obj
For Each obj In ActiveSheet.Shapes: obj.Delete: Next
'
' Delete the top rows
'
    Rows("1:8").Select
    Selection.Delete Shift:=xlUp
'
'Change tab name and back to FrontPage'

    Sheets("FrontPage").Select
    Range("K2").formula = "=SUBSTITUTE(MID(B2,53,9),"":"",""-"")"
'
'   this is where I want to have the tab named
'
    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("B:B").ColumnWidth = 66
    Sheets("FrontPage").Select
    Range("B1").Select
'
End Sub

 

[VoG]

Try

    Sheets("FrontPage").Select
    Range("K2").Formula = "=SUBSTITUTE(MID(B2,53,9),"":"",""-"")"
'
'   this is where I want to have the tab named
'
    On Error Resume Next
    ActiveSheet.Name = Range("K2").Value
    On Error GoTo 0
    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[MistyLogic]

Thanks for that. I did have to remove the line:

Sheets("FrontPage").Select

It was activating another sheet, I want the rest of the actions to take place on teh newly inserted sheet. That was probably there from my bad tinkering.

Thanks again!