Hello,

I put together a formula for a dynamic data validation list within excel. The file rebuilds calculations each month based on data that gets dropped into the file so the data validation range can change month to month. I have a macro in the file that clears the calculation tab and rebuilds it each month. When this code clears the page it breaks my dynamic data validation list...so I decided to build the dynamic formula into the code that clears and rebuilds the calculation. For some reason the code seems to run seamlessly but when I go to the data validation dropdown it isn't there. My code is below:

<>code
Sub BuildRecoverySummary()
Dim i As Long, LR As Long, a, ms As Worksheet
Application.ScreenUpdating = False
Set ms = Worksheets("Tenant Tax Recovery Summary")
ms.Range("A9:U" & Rows.Count).ClearContents
On Error Resume Next

With Worksheets("GLA")
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LR
If .Cells(i, "H") = vbNullString Or .Cells(i, "I") = vbNullString Then
a = Array(.Cells(i, "C").Value, .Cells(i, "D").Value, .Cells(i, "A").Value)
ms.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 3) = a
a = Array(.Cells(i, "E").Value, .Cells(i, "F").Value)
ms.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 2) = a
Else
a = Array(.Cells(i, "C").Value, .Cells(i, "D").Value, .Cells(i, "A").Value)
ms.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 3) = a
a = Array(.Cells(i, "E").Value, .Cells(i, "F").Value)
ms.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 2) = a
End If
Next i

End With
Application.CutCopyMode = 0
Application.ScreenUpdating = True
Set ms = Nothing
Dim LastRow As Long

With Worksheets("Tenant Tax Recovery Summary")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("H7:U7").Copy .Range("H9:U" & LastRow)
.Range("A7:A7").Copy .Range("A9:A" & LastRow)
End With
Rows("9:12").Select
Selection.Delete Shift:=xlUp
With Worksheets("Invoice")
Range("D10").Select
Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET('Tenant Tax Recovery Summary'!$H$9,0,0,COUNTA('Tenant Tax Recovery Summary'!$H:$H)-2,1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
<>code

Any help or suggestions would be greatly appreciated.