Name a worksheet after cell contents

[scotthannaford1973]

Hi

I have a bit of coding that copies an existing worksheet (called "SCHEMES") and puts it before another worksheet ("AUDIT") but I'd like to add in some code that will automatically take the content of cell A1 of the sheet that's copied across and automatically uses that as the worksheet name. Any ideas?

TIA


Sub BaselineCopyOfSchemes()

Sheets("SCHEMES").Select
Sheets("SCHEMES").Copy Before:=Sheets("AUDIT")

With ActiveWorkbook.ActiveSheet.Tab
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399975585192419
End With

MsgBox ("Now rename the worksheet tab to include the date" & vbNewLine & "Use the format YYYY-MM-DD" & vbNewLine & "e.g. 2020-06-19")


End Sub

 

[Domenic]

Try...

Sub BaselineCopyOfSchemes()

    Sheets("SCHEMES").Copy Before:=Sheets("AUDIT")
    
    With ActiveWorkbook.ActiveSheet
        .Name = .Range("a1").Value
        With .Tab
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.399975585192419
        End With
    End With
    
    MsgBox ("Now rename the worksheet tab to include the date" & vbNewLine & "Use the format YYYY-MM-DD" & vbNewLine & "e.g. 2020-06-19")


End Sub

Hope this helps!

 

[scotthannaford1973]

That's spot on - thanks!

Try...

Sub BaselineCopyOfSchemes()

    Sheets("SCHEMES").Copy Before:=Sheets("AUDIT")
    
    With ActiveWorkbook.ActiveSheet
        .Name = .Range("a1").Value
        With .Tab
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.399975585192419
        End With
    End With
    
    MsgBox ("Now rename the worksheet tab to include the date" & vbNewLine & "Use the format YYYY-MM-DD" & vbNewLine & "e.g. 2020-06-19")


End Sub

Hope this helps!

 

[Domenic]

You're very welcome, cheers!