Setting drop-down list validation by VBA in Excel

Question

I've got a VBA macro to copy two sheets (one a form to collect data, one a sheet with a batch of lookups for the drop-down menus) from a main workbook, populate the new workbook and save it out.

Because the drop-down data validation lists update their links when the sheet is copied, I have to reset the validation to refer to the new Lookups sheet. At the moment, I'm trying this (with varying coordinates, of course):

With wsNew.Cells(19, 5)  ' Display on web schedule
    .Value = wsData.Cells(11, iColCount).Value
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Lookups!$B$2:$B$3"
    .Validation.IgnoreBlank = True
    .Validation.InCellDropdown = True
End With

As soon as it gets to the .Validation.Add Type... line, it breaks with a 1004 ("Application-defined or object-defined error") error.

If there's any way to copy the sheet without it automatically updating the data validation that would be the ideal, but if there isn't, does anyone know what's causing that code to break and how to fix it?

Answer 1

Delete any prior data validation before attempting to create a new one.

With wsNew.Cells(19, 5)  ' Display on web schedule
    .Value = wsData.Cells(11, iColCount).Value
    .Validation.DELETE
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Lookups!$B$2:$B$3"
    .Validation.IgnoreBlank = True
    .Validation.InCellDropdown = True
End With