Print depending on tab colour

[Guest]

Is it possible to print only those tabs of a particular colour - say yellow?

Saintsman

 

[Guest]

Try this:-

Sub selectiveprint()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If ActiveSheet.Tab.ColorIndex = 6 Then
wSheet.PrintOut
End If
Next wSheet
End Sub


Mike

 

[Guest]

Thanks - all I need to do is group the sheets for page numbering & I'm there

 

[Guest]

Try this, I think you'll find it does the job for you. It does leave the
sheets with chosen color selected as a group.

Sub GroupSheetsByColorAndPrint()
'change ProperColor to color code you need
'13 is 'standard' yellow in Excel 2007
'6 is Yellow in earlier versions
'
'the array SheetsGroup() will always have
'an empty element as the last element,
'this code takes that into account
'
Const ProperColor = 6
Dim SheetsGroup() As String
Dim WS As Worksheet
ReDim SheetsGroup(1 To 1)
For Each WS In Worksheets
If WS.Tab.ColorIndex = ProperColor Then
SheetsGroup(UBound(SheetsGroup)) = WS.Name
ReDim Preserve SheetsGroup(1 To UBound(SheetsGroup) + 1)
End If
Next
If UBound(SheetsGroup) > 1 Then
ReDim Preserve SheetsGroup(1 To UBound(SheetsGroup) - 1)
Sheets(SheetsGroup).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
End Sub