insert sheet and naming sheet macro

[Dan88]

Hi all.
I am using a macro to insert a sheet to the end of workbook. Is there a code for me to name this sheet based on the value in column B that i have on another sheet named "details".
so if insert sheet and the values in "details" sheet's B1 is THANKYOU, my newly inserted sheet will be named THANKYOU.
B1 is not a static field, meaning i need the macro to read the latest info in column B before it names the new sheet.
Thanks in advance for advice.
Macro im using:
Sub Copy2End()
Dim ShName As String
Dim usrName As String, Authorized As Boolean
usrName = Environ("username")
If (usrName = "Sangdan") Or (usrName = "taylpe") Or (usrName = "sangdan") Or (usrName = "Taylpe") Then '
Authorized = True
Else
Authorized = False
End If
If Authorized Then
Dim ws As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
Set ws = WB.Sheets("Template")
ws.Copy After:=Sheets(WB.Sheets.Count)
End If
End Sub

 

[wddnld]

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets.Add
ws.Name = ActiveWorkbook.Sheets("details").Range("B1").Value

 

[Dan88]

WDDNLD, thanks for the reply.

i tried to add your code to mine and am receiving an error:

Sub rename()
Dim ShName As String
Dim usrName As String, Authorized As Boolean
usrName = Environ("username")
If (usrName = "Sangdan") Or (usrName = "taylpe") Or (usrName = "sangdan") Or (usrName = "Taylpe") Then '
Authorized = True
Else
Authorized = False
End If
If Authorized Then
Dim ws As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
Set ws = WB.Sheets("Template")
ws.Copy After:=Sheets(WB.Sheets.Count)
ws.Name = ActiveWorkbook.Sheets("Detail").Range("B1").Value
End If
End Sub

Please advise. This will rename the new sheet to the latest data in columb b on the "detail" sheet?

Thanks again.

 

[wddnld]

ws in my code refers to the new workbook from the Sheets.Add line. ws in your code refers to the existing Template sheet...not the Template that was copied.

If you are trying to copy the Template sheet and rename the copy to the value of B1 on the detail (details? you've said both) sheet, then try adding this:

    With ActiveWorkbook
        .Sheets(.Sheets.count).Name = .Sheets("detail").Range("B1").Value
    End With

after what you have:

    Dim ws As Worksheet, WB As Workbook
    Set WB = ActiveWorkbook
    Set ws = WB.Sheets("Template")
    ws.Copy After:=Sheets(WB.Sheets.count)

I'm not sure if there is a more direct way to rename the copied sheet in the same line that you copy it in, but this works.

 

[Dan88]

wddnld, thanks so much for your help.
Your code works wonderfuly, however, i need the sheet renaming to be the lastest data in detail sheet b column.
right now, it just renames it to value in details sheet b1, but i update the detail sheet daily and need the renaming to be the last value/data in column B of detail sheet.

anything i can change or add to get make this possible?
thanks again!
<SCRIPT type=text/javascript> vbmenu_register("postmenu_1630578", true); </SCRIPT>

 

[wddnld]

But you are renaming a sheet that you just copied, so at the time that the copy is made, the copy will have the newest name. Then the next time you run the code, another copy will be made and the name will be the most recent data in B1 on the detail sheet.

If you are trying to rename an existing sheet every time you look at it, you will need to add code to an event. You can update the sheet name on that sheet's activate event. In this case, you can do this in the specific sheet's code:

Private Sub Worksheet_Activate()
    Me.Name = ActiveWorkbook.Sheets("detail").Range("B1").Value
End Sub

You could also do it in Workbook_Open. I'm not sure what your process is and exactly what you're trying to do though.

 

[Dan88]

wddnld, i appreciate all your time.

Column B gets updated every so often. So B1 is not always the latest data entry. if there are 3 entries today, i would have added 3 sheet at the end of the day, and the 1st sheet would be named whatever the entry is on the detail sheet cell b1, the second sheet would be named whatever the entry is on the detail sheet cell b2, and so on...

 

[wddnld]

Ok, I misunderstood. Try this:

    Dim ws As Worksheet, wb As Workbook
    Dim rngData As Range
    Set wb = ActiveWorkbook
    With wb.Sheets("details")
        Set rngData = Range(.Range("B1"), .Range("B1").End(xlDown))
    End With
 
    For Each cell In rngData
        If cell.Value <> "" Then
            On Error Resume Next
 
            Set ws = Worksheets(cell.Value)
            If ws Is Nothing Then
                wb.Sheets("Template").Copy after:=wb.Sheets(wb.Sheets.count)
                wb.Sheets(wb.Sheets.count).Name = cell.Value
            Else
                Set ws = Nothing
            End If
 
            On Error GoTo 0
        End If
    Next cell

This will look through the data in column B on the detail sheet and add a tab for each value in B unless there is already a sheet with that name, in which case no sheet will be added.

 

[Dan88]

wddnld, do i add this to the bottom of my existing copy template macro, or is this the entire code? sorry for the newbie question.

if this is the entire vba, can you give please give me the entire code so i can copy and paste?

thanks

 

[wddnld]

Paste it over everything between "If Authorized Then" and "End If"