Rename active sheet based on cell value.

[Dagoom]

Hello all,

I was wondering if anyone can help me out here.

I have a few macros set up to copy a sheet, and add a row to my table.

Now what I need to know, can I rename my active sheet based on the bottom value in a range?

I have associated my table as "Table1" and what Would be great is if I could rename a sheet the bottom value in Column A.

Please help me out here, I am so close to finally finishing this project.

Thanks for your time.

[alansidman]

Have a look at this link:

http://www.ozgrid.com/forum/showthread.php?t=65841

[Dagoom]

Thanks Alan, is there any way to have it select the last cell with data in it in a range?

[alansidman]

Yes. Try this:

Option Explicit 
 
Sub add_Sheet_name() 
    Dim sShtName As String
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row

     
    sShtName =   Range("A" & lr).Value
     
    If Not WksExists(sShtName) Then Worksheets.Add After:=Worksheets(Worksheets.Count) 
     
    ActiveSheet.Name = sShtName 
End Sub 
Function WksExists(wksName As String) As Boolean 
    On Error Resume Next 
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0) 
End Function

[Dagoom]

Unfortunately what it does is make a new sheet, not just rename the active one.

Is there any way to add renaming the sheet to this macro?

All of my sheet names are numbered 1-100, this macro creates a page from a template, adds a row to my table and now all I need to do is make it rename the page after the new row, which in this case would have "101" as a name.

Sub AddRow_CopySheet_Rename()
    Application.ScreenUpdating = False
    Dim Sheet2 As Variant
    Set Sheet2 = Worksheets("Template")
    Sheet2.Visible = True
    Dim CellX As Range
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Range("InsertSection").Select
    Dim WS As Worksheet, WB As Workbook
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template")
    WS.Copy After:=Sheets(WB.Sheets.Count)
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
End Sub

Thanks for your help Alan. I really appreciate it.

Right now my new sheet is called "Template (2)".

I would like it to be named 101 in this case.

Then the next sheet when I run the macro would be 102.

[protonLeah]

Are you adding a new sheet and then renaming it, or just renaming an existing sheet?

[Dagoom]

I am adding a sheet with my one macro, then was planning on adding the second macro to the first to rename it using the last used cell in my table, or range of A column.

[protonLeah]

Try this mods using alansidman's code above:

Option Explicit
Sub AddRow_CopySheet_Rename()
    Application.ScreenUpdating = False
    Dim Sheet2 As Variant
    Dim CellX As Range
    Dim WS As Worksheet, WB As Workbook
'-----------------------------
    Dim sShtName As String
    Dim lr As Long
'------------------------------
    Set Sheet2 = Worksheets("Template")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("Template")
    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    ActiveSheet.Name = sShtName
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
End Sub

[Dagoom]

THANK YOU SO MUCH!

That works amazingly.

You are the man.

[Dagoom]

Is there any way to make it so if the number in Column A is below 99, it adds a different template?

If not it's not a huge deal, you guys have done more than enough for me.

Oops, sorry for the double post.

[protonLeah]

Something like:

Option Explicit
Sub AddRow_CopySheet_Rename()
    Application.ScreenUpdating = False
    Dim Sheet2 As Variant
    Dim CellX As Range
    Dim WS As Worksheet, WB As Workbook
'-----------------------------
    
    Dim sShtName As String
    Dim lr As Long
    
'------------------------------
    Set Sheet2 = Worksheets("Template")
    Sheet2.Visible = True
    Set CellX = ActiveCell
    ActiveWorkbook.Sheets(1).Activate
    Range("Table2").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
'-------------------------------
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    sShtName = Range("A" & lr).Value
    
'-------------------------------
    Range("InsertSection").Select
    Set WB = ActiveWorkbook

    Select Case lr
        Case Is < 99
            Set WS = WB.Sheets("template2")
        Case Is >= 99
            Set WS = WB.Sheets("Template")
    End Select

    WS.Copy After:=Sheets(WB.Sheets.Count)
'-------------------------------
    
    ActiveSheet.Name = sShtName
    
'-------------------------------
    Application.Goto CellX
    Sheet2.Visible = False
    Application.ScreenUpdating = True
End Sub