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

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
80
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Code for the button

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

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

Board Regular
Joined
Mar 15, 2018
Messages
80
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

Board Regular
Joined
Mar 15, 2018
Messages
80
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

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

Board Regular
Joined
Mar 15, 2018
Messages
80
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Mar 15, 2018
Messages
80
I checked the properties and print option is not checked. The command button on the new sheets is just misleading for other people.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Select option : "Don't move or size with cells"

 

Watch MrExcel Video

Forum statistics

Threads
1,108,808
Messages
5,524,995
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top