[SOLVED] Need Macro to Hide & Unhide Tabs by Name or Color

Sub data_tabs()
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "*Data*" And ws.Tab.Color = RGB(225, 51, 153) Then
ws.Visible = Not ws.Visible
End If
Next
End Sub

Sub formatted_tabs()
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "*Data*" Or Not ws.Tab.Color = RGB(225, 51, 153) Then
ws.Visible = Not ws.Visible
End If
Next
End Sub

Thanks a ton! The second code does both the hiding and unhiding, the top code isn't doing anything that I can see - but it works! The only thing is all my pink tabs are now at the end instead of being next to the corresponding red tab. Not a huge deal, but do you know a way of leaving the tabs in the same order?

Thanks again!

Lianne

Can you post the workbook (stripped of data, just need to see the tabs)?

Here you go!Blank_MC_Report.xlsm

Try these. They work as expected for me. I cannot replicate your issue of the pink tabs being at the end; the order remains the same for me after hiding/unhiding, also as expected. Hiding shouldn't have anything to do with tab ordering. Hopefully that issue is solved with this new code.

Also of note, a couple of your pink tabs don't have "Data" in them (either as a misspelling or just a "D"). You could remove the Data requirement and just look at color if you want.

Sub data_tabs()
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "*Data" And ws.Tab.Color = 10040319 Then
ws.Visible = Not ws.Visible
End If
Next
End Sub

Sub formatted_tabs()
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "Data*" And Not ws.Tab.Color = 10040319 Then
ws.Visible = Not ws.Visible
End If
Next
End Sub

Thanks! Worked like a charm :-)

Walruss.

I would like to possibly do something similar. But can you explain how you can determine what the tab color is? Ie, what color would 10040319 be?

lianne:

Glad to help. Please mark this thread as SOLVED, and any reputation points (the * in the lower left of my posts) is appreciated.

ptmuldoon:

Put a Watch on ws.Tab.Color (highlight and hit F9, or Debug -> Watch) and ws.Name, then step through the code (F8). For each worksheet name (ws.Name), you'll be able to see the tab color number.