VBA Copy and Rename 2 Worksheets with current date but different shifts.

[Jeff S]

Hello,
Having issues with 1 button macro on a menu worksheet that copies 2 separate worksheets and places a cell value on them.
Example:

It would be executed from Staff Summary Sheet and copy then rename the Working Sheets.
I get it to copy but I somehow mess up with the renaming part.

 

[Joe4]

Welcome to the Board!

Please post your VBA code for us to analyze.
Note that there are instructions for how to post your VBA code here: How to Post Your VBA Code

 

[Jeff S]

Thanks Joe, sorry about that. Here it is, but still messes up at the first active sheet. The Msg boxes are in for me to see the current active sheet.. It helped on an earlier problem I had, but fixed. Not required now.

Sub TABCOPY1()
'
' TABCOPY1 Macro
'
   
'
    Sheets("Gold-WORKING").Select
    Sheets("Gold-WORKING").Copy Before:=Sheets(1)
    MsgBox "The name of the active sheet is " & ActiveSheet.Name
    ActiveSheet.Name = ActiveSheet.Range("E1").Value
        Sheets("Blue-WORKING").Select
        Sheets("Blue-WORKING").Copy Before:=Sheets(1)
        MsgBox "The name of the active sheet is " & ActiveSheet.Name
        ActiveSheet.Name = ActiveSheet.Range("E1").Value
            Sheets("Staff Summary").Select
End Sub

 

[Joe4]

OK, what exactly is in cell E1?

Can you walk us through a simple example, explaining exactly what you want to happen, and telling us what actually is happening for that particular example?

 

[Jeff S]

OK, what exactly is in cell E1?

Can you walk us through a simple example, explaining exactly what you want to happen, and telling us what actually is happening for that particular example?

The manager will update the Staff Summary Sheet and at the end of the week will click on the macro button. I provide the macro to take a snapshot (copy) of the staffing for both shifts Gold and Blue. I have formatted the cells (E1) on both shifts Tabs to give the shift "Blue-" or "Gold-" then the start of the week mmm-dd-yyyy. They are doing this manually (Thus the picture), but would like an easier method.

 

[Joe4]

Here is code that will automatically use today's date without any input from the user:

Sub TABCOPY1()
'
' TABCOPY1 Macro
'
    Dim shtPfx As String
    Dim arr() As String
    Dim newName As String
    
    Sheets("Gold-WORKING").Select
    
'   Build new sheet name by getting prefix and adding current date
    arr = Split(ActiveSheet.Name, "-")
    newName = arr(0) & Format(Date, "-m-d-yy")

    Sheets("Gold-WORKING").Copy Before:=Sheets(1)
    ActiveSheet.Name = newName

End Sub

You mentioned "Start of the week", but then used date 4-4-23, which is a Tuesday.
If you can define actually when the "Start of the week" happens, we can probably amend the code to do that.

 

[Jeff S]

Here is code that will automatically use today's date without any input from the user:

Sub TABCOPY1()
'
' TABCOPY1 Macro
'
    Dim shtPfx As String
    Dim arr() As String
    Dim newName As String
   
    Sheets("Gold-WORKING").Select
   
'   Build new sheet name by getting prefix and adding current date
    arr = Split(ActiveSheet.Name, "-")
    newName = arr(0) & Format(Date, "-m-d-yy")

    Sheets("Gold-WORKING").Copy Before:=Sheets(1)
    ActiveSheet.Name = newName

End Sub

You mentioned "Start of the week", but then used date 4-4-23, which is a Tuesday.
If you can define actually when the "Start of the week" happens, we can probably amend the code to do that.

The manager when copying it started his week on the 4th and copied it that way by accident. It should be every Monday even if a holiday.
Sorry for confusion. I just started this position in Jan 23 and trying to remember VBA coding from 2 decades ago in an Auto Plant is testing my knowledge and memory limits.

 

[Joe4]

OK, try this version:

Sub TABCOPY1()
'
' TABCOPY1 Macro
'
    Dim shtPfx As String
    Dim arr() As String
    Dim newName As String
    
    Sheets("Gold-WORKING").Select
    
'   Build new sheet name by getting prefix and adding current date
    arr = Split(ActiveSheet.Name, "-")
    newName = arr(0) & Format(Date - (7 - Weekday(Date, vbMonday)), "-m-d-yy")

    Sheets("Gold-WORKING").Copy Before:=Sheets(1)
    ActiveSheet.Name = newName

End Sub

 

[Jeff S]

OK, try this version:

Sub TABCOPY1()
'
' TABCOPY1 Macro
'
    Dim shtPfx As String
    Dim arr() As String
    Dim newName As String
   
    Sheets("Gold-WORKING").Select
   
'   Build new sheet name by getting prefix and adding current date
    arr = Split(ActiveSheet.Name, "-")
    newName = arr(0) & Format(Date - (7 - Weekday(Date, vbMonday)), "-m-d-yy")

    Sheets("Gold-WORKING").Copy Before:=Sheets(1)
    ActiveSheet.Name = newName

End Sub

That did it, now I repeat for Blue and that should fix it. I would never have remembered that. Thanks Joe. I tried so many ways to make this work and remember doing it years ago. Old age I guess. Your a life saver. Thank you!

 

[Joe4]

You are welcome!
Glad I was able to help!