VBA new tab colour

[GedSalter]

Hi,

I have a VBA to create a new tab and also create a PDF of the page too, as well as protecting the new tab sheet. What I am trying to do is add to this VBA so that the new tab colour is different from original. I want to do this as I want to hide all the created tabs and leave the first 2 original tabs in view.

This is what I have so far

Sub AddSheet()
Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)



ActiveSheet.Copy After:=Worksheets(Sheets.Count)
ws.Tab.Color = 6

Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value


ActiveSheet.Protect

End If


strPath = ActiveWorkbook.Path & "\Invoices\"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & Range("E9"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub



Hope someone can help

 

[Fluff]

Are you trying to make the new sheets tab black?

 

[GedSalter]

Are you trying to make the new sheets tab black?

doesnt matter what colour as long as they are different from the two I have. The problem is the new tabs are created from a macro on another tab. When the new tabs change colour then the original tab changes to the same colour.

Ultimately I want to hide new tabs so maybe I should put hide in the macro for new tabs. How can I do that without hiding the original tab?


regards

Ged

 

[Fluff]

The problem is the new tabs are created from a macro on another tab.

That's not a problem you can simply modify the code to change the tab colour when you create them

When the new tabs change colour then the original tab changes to the same colour.

In that case if you want different coloured tabs, you will need to delete that code.

Ultimately I want to hide new tabs

Then what does it matter if the tabs are the same colour or not?

 

[GedSalter]

how would I be able to hide all worksheets except 2 whose names are "invoice" and "data"

 

[Fluff]

Like

Sub GedSalter()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Name <> "Invoice" And Ws.Name <> "Data" Then
         Ws.Visible = xlSheetHidden
      End If
   Next Ws
End Sub

Note: This is case sensitive.

 

[GedSalter]

I tried putting that into my existing macro and couldnt get it to work. I am not good at macros

 

[Fluff]

In that case can you please post your code.