This seems to work:
On the Settings sheet, add another employee called All Employees.
On Employee Leave Tracker, right-click Back to Calendar View, select Format Shape, select Properties, and change Object Positioning to "Don't move or size with cells".
Select column H, and in the name box, give it the name SaveEmployees.
Press F11 to open the VBA window, and in the Project Explorer, double-click Sheet3 (Calendar View) to open the code window for that sheet. Into there, copy and paste the following code:
Save your workbook. Then, on Calendar View, select All Employees to see the results.![]()
Private Sub Worksheet_Change(ByVal Target As Range) Const AllEmps = "All Employees", SaveEmps = "SaveEmployees" If Target.Name = "='Calendar View'!$AN$3" Then With Sheets("Employee Leave Tracker") If Target.Value = AllEmps Then .Columns("B:B").Copy Destination:=.Range(SaveEmps) .Range("B4", "B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value = AllEmps Else If .Range(SaveEmps).Range("A4").Value <> "" Then .Range(SaveEmps).Copy Destination:=.Columns("B:B") .Range(SaveEmps).ClearContents End If End If End With End If End Sub
Bookmarks