insert sheet and naming sheet macro

Dan88

Active Member
Joined
Feb 14, 2008
Messages
255
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

Board Regular
Joined
Jul 14, 2008
Messages
51
Code:
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets.Add
ws.Name = ActiveWorkbook.Sheets("details").Range("B1").Value
 

Dan88

Active Member
Joined
Feb 14, 2008
Messages
255
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

Board Regular
Joined
Jul 14, 2008
Messages
51
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:
Code:
    With ActiveWorkbook
        .Sheets(.Sheets.count).Name = .Sheets("detail").Range("B1").Value
    End With
after what you have:
Code:
    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

Active Member
Joined
Feb 14, 2008
Messages
255
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>
 
Last edited:

wddnld

Board Regular
Joined
Jul 14, 2008
Messages
51
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:

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

Active Member
Joined
Feb 14, 2008
Messages
255
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

Board Regular
Joined
Jul 14, 2008
Messages
51
Ok, I misunderstood. Try this:

Code:
    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

Active Member
Joined
Feb 14, 2008
Messages
255
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
 

Forum statistics

Threads
1,082,096
Messages
5,363,121
Members
400,719
Latest member
Oliver12

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top