Sub Refresh()
'
' Refresh Macro
'
'Save a copy with date stamp before refreshing data
Dim dtToday As String
dtToday = Format(Date, "yyyymmdd")
ActiveWorkbook.SaveCopyAs Filename:="\\NABDC01SDPRS01\C01905_SHARE_S_01\TOR1\Resource\Service Ontario RESP Tracker\Archived\RESP Leads List_" & dtToday & ".xlsm"
'
Dim reccnt As Integer
ActiveWorkbook.Connections("Query - service_ontario_master_list").Refresh
'Added logic in hope that it will wait for connectino to refresh before running the rest of code
ActiveWorkbook.Save
Sheets("Working List").Select
'Unprotect sheet
ActiveSheet.Unprotect
'Added logic to remove all filter before delete. This should resolve the duplicate issue
ActiveWorkbook.Worksheets("Working List").ListObjects("Table2").AutoFilter.ShowAllData
Cells.Select
Selection.EntireColumn.Hidden = False
reccnt = Range("A3") + 1
Range("A5:AO1000").Select
Selection.clear
Sheets("Query").Select
Range(Cells(2, 1), Cells(reccnt, 40)).Select
Selection.Copy
Sheets("Working List").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("S3").Select
Application.CutCopyMode = False
Selection.Copy
Range("S5").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(Cells(5, 22), Cells(5 + reccnt, 22)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$D$3:$D$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 29), Cells(5 + reccnt, 29)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$D$3:$D$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 35), Cells(5 + reccnt, 35)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$D$3:$D$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("AA5").Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$B$3:$B$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 27), Cells(5 + reccnt, 27)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$B$3:$B$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 33), Cells(5 + reccnt, 33)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$B$3:$B$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 39), Cells(5 + reccnt, 39)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$B$3:$B$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(Cells(5, 3), Cells(5 + reccnt, 3)).Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$K$3:$K$4"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'Add data validation (drop down) for Status and Outcome
Range("T5").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$N$3:$N$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("U5").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Lists!$B$3:$B$5"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'Sort list: lead arrival data aesc and UID aesc
ActiveWorkbook.Worksheets("Working List").ListObjects("Table2").sort.SortFields _
.clear
ActiveWorkbook.Worksheets("Working List").ListObjects("Table2").sort.SortFields _
.Add2 Key:=Range("Table2[Lead Arrival Date]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Working List").ListObjects("Table2").sort.SortFields _
.Add2 Key:=Range("Table2[UID]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Working List").ListObjects("Table2").sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Hide calculation columns
Range("A:B").EntireColumn.Hidden = True
Columns("F:G").Select
Selection.EntireColumn.Hidden = True
Range("Y:Y").EntireColumn.Hidden = True
'Freeze Panel
Range("H5").Select
ActiveWindow.FreezePanes = True
'Protect Sheet
Columns("T:AN").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V1:AN4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Columns("C:C").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("C1:C4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Columns("D:U").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
'Date Cloumns Adjustment
Columns("W:W").ColumnWidth = 13
Columns("W:W").NumberFormat = "m/d/yyyy"
Columns("AD:AD").ColumnWidth = 13
Columns("AD:AD").NumberFormat = "m/d/yyyy"
Columns("AJ:AJ").ColumnWidth = 13
Columns("AJ:AJ").NumberFormat = "m/d/yyyy"
Range("V4").Select
End Sub
