Good day
I am working on a vacation tracking workbook that references one input page across multiple tabs. Once the user enters the associate's name and qualifications in the Input tab, the Jan-Dec tabs populate with this information. What I am trying to do is to offer the user an option to remove an associate if they resign or retire via a message box. I can get this to work however the results are not as expected. The associate's name is removed from the Inputs tab as well as the monthly tabs however when it is sorted, it puts a 0 in the top cell A3 on the inputs tab and the header on the Jan tab is missing. Any help would be appreciated.
Sub Remove_AN_ASSOCIATE()
Dim MESSAGE As String
Dim RNG As Range
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Inputs").Select
Let MESSAGE = InputBox("Enter the associate.", "Associate Removal Form")
If MESSAGE = vbNullString Then Exit Sub
For Each RNG In Range("A3:M358")
If RNG = MESSAGE Then RNG.ClearContents
Next
Range("A3:M358").Sort Key1:=Range("A3:M358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jan").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Feb").Select
For Each RNG In Range("A3:AD358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AD358").Sort Key1:=Range("A3:AD358"), Order1:=xlAscending, Header:=xlNo
Sheets("Mar").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Apr").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("May").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jun").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jul").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Aug").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Sep").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Oct").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Nov").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Dec").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I am working on a vacation tracking workbook that references one input page across multiple tabs. Once the user enters the associate's name and qualifications in the Input tab, the Jan-Dec tabs populate with this information. What I am trying to do is to offer the user an option to remove an associate if they resign or retire via a message box. I can get this to work however the results are not as expected. The associate's name is removed from the Inputs tab as well as the monthly tabs however when it is sorted, it puts a 0 in the top cell A3 on the inputs tab and the header on the Jan tab is missing. Any help would be appreciated.
Sub Remove_AN_ASSOCIATE()
Dim MESSAGE As String
Dim RNG As Range
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Inputs").Select
Let MESSAGE = InputBox("Enter the associate.", "Associate Removal Form")
If MESSAGE = vbNullString Then Exit Sub
For Each RNG In Range("A3:M358")
If RNG = MESSAGE Then RNG.ClearContents
Next
Range("A3:M358").Sort Key1:=Range("A3:M358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jan").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Feb").Select
For Each RNG In Range("A3:AD358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AD358").Sort Key1:=Range("A3:AD358"), Order1:=xlAscending, Header:=xlNo
Sheets("Mar").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Apr").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("May").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jun").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Jul").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Aug").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Sep").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Oct").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Sheets("Nov").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo
Sheets("Dec").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub