Hi Everyone
I want to add color to all tabs of all worksheets. Currently the code all add color to Sheet 1.
Any assistance will be much appreciated.
Sub CombineCodes()
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long, iCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, TitleRow As Long
Application.ScreenUpdating = False
vCol = 1
Set ws = Sheets("Sheet1")
vTitles = "A1:Z1"
TitleRow = Range(vTitles).Cells(1).Row
LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
iCol = ws.Columns.Count
ws.Cells(1, iCol) = "key"
For Itm = 2 To LR
On Error Resume Next
If ws.Cells(Itm, vCol) <> "" And Application.WorksheetFunction _
.Match(ws.Cells(Itm, vCol), ws.Columns(iCol), 0) = 0 Then
ws.Cells(ws.Rows.Count, iCol).End(xlUp).Offset(1) = ws.Cells(Itm, vCol)
End If
Next Itm
ws.Columns(iCol).Sort Key1:=ws.Cells(2, iCol), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
MyArr = Application.WorksheetFunction.Transpose _
(ws.Columns(iCol).SpecialCells(xlCellTypeConstants))
ws.Columns(iCol).Clear
ws.Range(vTitles).AutoFilter
For Itm = 2 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm) & ""
If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then 'create sheet if needed
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(Itm) & ""
Else 'clear sheet if it exists
Sheets(MyArr(Itm) & "").Move After:=Sheets(Sheets.Count)
Sheets(MyArr(Itm) & "").Cells.Clear
End If
ws.Range("A" & TitleRow & ":A" & LR).EntireRow.Copy _
Sheets(MyArr(Itm) & "").Range("A1")
ws.Range(vTitles).AutoFilter Field:=vCol
MyCount = MyCount + Sheets(MyArr(Itm) & "").Range("A" & Rows.Count) _
.End(xlUp).Row - Range(vTitles).Rows.Count
ws.Range("A1:Z1").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("E1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ws.Tab.ColorIndex = 3
Sheets(MyArr(Itm) & "").Columns.AutoFit
Next Itm
Application.ScreenUpdating = True
End Sub
Bookmarks