Bit more than I originally thought it would be:
Sub RunMe()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim Sh As Worksheet
Dim rCell As Range
Dim lastrow As Long
Dim arrSh() As String
Dim bDim As Boolean
Dim i As Integer
Application.ScreenUpdating = False
bDim = False
lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Range("B1:B" & lastrow).AdvancedFilter xlFilterInPlace, , , True
'populate array with sheet names
For Each rCell In ws.Range("B2:B" & lastrow).SpecialCells(xlCellTypeVisible)
If bDim = False Then
ReDim arrSh(0 To 0) As String
arrSh(0) = rCell.Value
bDim = True
Else
ReDim Preserve arrSh(0 To UBound(arrSh) + 1) As String
arrSh(UBound(arrSh)) = rCell.Value
End If
Next rCell
ws.ShowAllData
ws.Range("A1").EntireColumn.EntireRow.Hidden = False
For i = LBound(arrSh) To UBound(arrSh)
If Not Evaluate("ISREF('" & arrSh(i) & "'!A1)") Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = arrSh(i)
Set Sh = Sheets(arrSh(i))
With ws
.AutoFilterMode = False
.Range("B1:B" & lastrow).AutoFilter 1, arrSh(i)
.AutoFilter.Range.EntireRow.Copy Sh.Range("A1")
.AutoFilterMode = False
End With
With Sh.Columns("C:C")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0", Formula2:="=25"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=26", Formula2:="=50"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Font.ColorIndex = 7
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=51", Formula2:="=75"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Font.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=76", Formula2:="=100"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Font.ColorIndex = 4
End With
End If
Next i
Erase arrSh
Application.ScreenUpdating = True
End Sub
Bookmarks