CODEWITHSUNDEEP
excelvba
luke
luke

Reputation: 482

Excel VBA Store/Dim sheet tab color

I use this code to delete the active sheet and then copy a different sheet then rename the copied sheet the same as the deleted sheet, How can I also change the sheet tab color on the copied sheet to match the deleted sheet.

Dim ShtName As String
Dim ws As Worksheet
Application.DisplayAlerts = False

ShtName = ActiveSheet.NAme
ActiveSheet.Delete

Dim NewSht  As Worksheet

Master_Work_Order.Copy After:=ActiveSheet

Set NewSht = ActiveSheet

'set new copied sheet name to Previous ActiveSheet's name
NewSht.NAme = ShtName

Upvotes: 1

Views: 340

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Use Sheet.Tab.Color 

Sub test()

    Dim ShtName As String
    Dim ws As Worksheet

    Dim oldTabColor

    Application.DisplayAlerts = False

    ShtName = ActiveSheet.Name

    '/ Store Old Tab Color

    oldTabColor = ActiveSheet.Tab.Color

    ActiveSheet.Delete

    Dim NewSht  As Worksheet

    Master_Work_Order.Copy After:=ActiveSheet

    Set NewSht = ActiveSheet

    'set new copied sheet name to Previous ActiveSheet's name
    NewSht.Name = ShtName


     NewSht.Tab.Color = oldTabColor


End Sub

Upvotes: 4

Related Questions