Copy Master Sheet and save to end, then rename the sheet when data is changed in a cell

[bhalbach]

This is likley very simple for someone but not me...I have looked through several posts and cannot find what I am seeking.

I have a workbook with a Sheet named "Master" that I use for a biweekly timesheet. I currently copy this sheet to the end and rename it manually.

I want to automate this so that I can copy the master to the end of the workbook (command button?) and rename it ideally with the value that I input into cell B5. Any time the value is changed in B5 I would like the sheet to be renamed. I will eventually have 26 sheets in the workbook.

Any help and suggestions would be great.

 

[DanteAmor]

Code for the button

Sub Copy_Master()
    Dim sh As Worksheet, ws As Worksheet
    Dim wName As String
    Set ws = Sheets("Master")
    wName = ws.Range("B5").Value
    
    'Validations
    If wName = "" Then
        MsgBox "Enter the name of the sheet"
        Exit Sub
    End If
    For Each sh In Sheets
        If LCase(sh.Name) = LCase(wName) Then
            exist = True
            Exit For
        End If
    Next
    If exist Then
        MsgBox "There is already a sheet with the name : " & wName
        Exit Sub
    End If
    
    ws.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = wName
        
    MsgBox "Master sheet copied"
End Sub

-----------------------

Any time the value is changed in B5 I would like the sheet to be renamed. I will eventually have 26 sheets in the workbook.

Put the following code in the events of your Master Sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        wName = Target.Value
        For Each sh In Sheets
            If LCase(sh.Name) = LCase(wName) Then
                exist = True
                Exit For
            End If
        Next
        If exist Then
            MsgBox "There is already a sheet with the name : " & wName
            Exit Sub
        End If
        
        Sheets(Sheets.Count).Name = wName
    End If
End Sub

Right click the tab of the Master sheet, select view code & paste the code into the window that opens up.

 

[bhalbach]

Does not seem to work like it needs to.


-Master sheet has no contents in cell B5
-Click control button located on Master sheet and it asks for a name.
-if cell b5 contains a name a new sheet will be created & named correctly
-when trying to make another sheet, I have to change the name in B5...which renames the first sheet that was created.​

 

[bhalbach]

if I enter a name in B5 before doing anything else on the master, the Master sheet name is changed to the contents of B5

 

[DanteAmor]

Remove this macro.
Then put the name in B5 and press the button for each new sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        wName = Target.Value
        For Each sh In Sheets
            If LCase(sh.Name) = LCase(wName) Then
                exist = True
                Exit For
            End If
        Next
        If exist Then
            MsgBox "There is already a sheet with the name : " & wName
            Exit Sub
        End If
        
        Sheets(Sheets.Count).Name = wName
    End If
End Sub

 

[bhalbach]

Works like a charm. Thank you kindly

The one thing I dislike about how this works is that all the new sheets that are created have the command button on them, which only works from the Master sheet. Is there a way to delete the command button when the new sheets are created with VBA?

I have another thing I want to accomplish with this workbook if you wouldn't mind helping with.
-worksheet called "Summary"
-the Summary worksheet collects data from all the worksheets that are created in the workbook.
-Summary worksheet would need to look at every worksheet in the workbook, in Column A and find where there is a date
-There will typically be 2 rows in every worksheet that will have a Date in Column A
-The row that the date is found on, I need the data copied and pasted as follows to the Summary sheet starting on Row 2 (Row 1 will have headers)
...Column A (date) to Summary Column A
...Column I to Summary Column B
...Column J to Summary Column C
...Column K to Summary Column D
...Column L to Summary Column E
...Column M to Summary Column F
...Column N to Summary Column G
...Column O to Summary Column H
and so on all the way to Column Y

I greatly appreciate your assistance.​

 

[DanteAmor]

Works like a charm. Thank you kindly

The one thing I dislike about how this works is that all the new sheets that are created have the command button on them, which only works from the Master sheet. Is there a way to delete the command button when the new sheets are created with VBA?

​

In the button properties, uncheck the print object option

 

[bhalbach]

I checked the properties and print option is not checked. The command button on the new sheets is just misleading for other people.​

 

[DanteAmor]

Select option : "Don't move or size with cells"