Sub UpdateSchedule()
'
'
' Update Schedule Macro
Range("C13:C60").Select
Range("C60").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("C60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("C13:C60")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' SortPieman Macro
'
Range("E13:E60").Select
Range("E60").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("E60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("E13:E60")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' SortWaiter Macro
'
Range("G13:G60").Select
Range("G60").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("G60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("G13:G60")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' SortBusser Macro
'
Range("I13:I60").Select
Range("I60").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("I60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("I13:I60")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'' SortDriver Macro
'
Range("K13:K60").Select
Range("K60").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("K60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("K13:K60")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C13:C60").Select
ActiveSheet.Unprotect
Range("N151:U151").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.unmerge
Range("C13:C60").Select
Selection.Copy
Range("N23").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E13:E60").Select
Selection.Copy
Range("N70").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("G13:G60").Select
Selection.Copy
Range("N118").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("I13:I60").Select
Selection.Copy
Range("N166").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("K13:K60").Select
Selection.Copy
Range("N214").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("N23:N275").Select
ActiveSheet.Range("$N$23:$N$275").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key _
:=Range("N261"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
.SetRange Range("N23:N261")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N22:N150").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99786370433668E-02
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWindow.SmallScroll Down:=129
Range("N151:U151").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99786370433668E-02
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Dim x, y, i As Long, e, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
x = [if((X7:BS55<>"")*(countif(c13:k95,X7:BS55)=0),X7:BS55,char(2))]
For i = 1 To UBound(x)
y = Filter(Application.Index(x, i, 0), Chr(2), 0)
If UBound(y) > -1 Then
For Each e In y
dic(e) = Empty
Next
End If
Next
Range("C8").Select
If dic.Count Then MsgBox "Please Unschedule" & vbLf & Join(dic.keys, vbLf), 16
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Bookmarks