Sub RePopulate()
Dim cNames As Collection
Dim oSN As clsSheetManager
Dim shtAll As Object, rowAll As Long, colAll As Long
Dim shtN As Object, rowN As Long, colN As Long
Dim shtPrev As Object
Dim sManager As String, sPrevManager As String
Call Refresh_All
Set shtAll = Sheets("All")
Set cNames = New Collection
rowAll = 2
Do While Trim$(shtAll.Cells(rowAll, 2)) <> ""
sManager = shtAll.Cells(rowAll, 2)
If sManager <> sPrevManager Then
Call AddNames(cNames, sManager, Left$(sManager, 1 + InStr(1, sManager, " ", vbTextCompare)))
End If
sPrevManager = sManager
rowAll = rowAll + 1
Loop
shtAll.Columns("A:A").Select
Selection.NumberFormat = "d-mmm-yyyy"
Sheets("All").Select
shtAll.Cells.Select
shtAll.Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
Set shtPrev = shtAll
For Each oSN In cNames ' For each manager in the collection.
rowN = 0
colN = 0
rowAll = 2
On Error Resume Next
Set shtN = Sheets(oSN.Sheet)
If Err.Number = 0 Then 'found so delete and re-create
Application.DisplayAlerts = False
Call Sheets(oSN.Sheet).Delete
' Call sht.Delete 'Sheets.Delete(oSN.Sheet)
Application.DisplayAlerts = True
End If
On Error GoTo 0
Set shtN = Sheets.Add(, shtPrev)
Set shtPrev = shtN
shtN.Name = oSN.Sheet
colAll = 1
Do While Trim$(shtAll.Cells(1, colAll)) <> ""
colN = colAll
shtN.Cells(1, colN) = shtAll.Cells(1, colAll)
colAll = colAll + 1
Loop
rowAll = 2
rowN = 2
Do While Trim$(shtAll.Cells(rowAll, 2)) <> ""
colAll = 1
colN = 1
If StrConv(Trim$(shtAll.Cells(rowAll, 2)), vbUpperCase) = _
StrConv(Trim$(oSN.Manager), vbUpperCase) Then ' Manager name matches the require one.
If StrConv(Trim$(shtAll.Cells(rowAll, 3)), vbUpperCase) <> _
StrConv(Trim$(shtAll.Cells(rowAll - 1, 3)), vbUpperCase) Then
rowN = rowN + 1
End If
Do While Trim$(shtAll.Cells(rowAll, colAll)) <> ""
colN = colAll
shtN.Cells(rowN, colN) = shtAll.Cells(rowAll, colAll)
colAll = colAll + 1
Loop
rowN = rowN + 1
End If
rowAll = rowAll + 1
Loop
shtN.Columns("A:A").Select
Selection.NumberFormat = "d-mmm-yyyy"
shtN.Columns("D:D").Select
Selection.NumberFormat = "0.00"
shtN.Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
shtN.Cells.Select
shtN.Cells.EntireColumn.AutoFit
shtN.Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Next oSN
End Sub
Sub Refresh_All()
'
' Keyboard Shortcut: Ctrl+r
For n = 3 To 13
Sheets(n).Delete
Next
Sheets("All").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
