VBA to redefine named ranges breaks formulas

[Fishboy]

Hi all,

I have an Excel 2010 workbook in which I am using a Workbook_BeforeClose event to do a number of things. The part I am having trouble with basically is designed to remove all existing named ranges, then recreate them based on the rules in the code. The reason for this is because users tend to have a habit of deleting rows that they deem unnecessary, but every time they do so my named ranges decrease by one row. My code allows for the named ranges to be redefined to the correct size every time the workbook closes.

ActiveWorkbook.Names("Warehouse_ACAgent").Delete
ActiveWorkbook.Names("Warehouse_ACBookingStatus").Delete
ActiveWorkbook.Names("Warehouse_ACDate").Delete
ActiveWorkbook.Names("Warehouse_ACOutcome").Delete
ActiveWorkbook.Names("Warehouse_ACWithdrawalReason").Delete
ActiveWorkbook.Names("Warehouse_ApplicationDate").Delete
ActiveWorkbook.Names("Warehouse_ApplicationMethod").Delete
ActiveWorkbook.Names("Warehouse_CandidateName").Delete
ActiveWorkbook.Names("Warehouse_Origin").Delete
ActiveWorkbook.Names("Warehouse_Reserves").Delete
ActiveWorkbook.Names("Warehouse_ScreeningAgent").Delete
ActiveWorkbook.Names("Warehouse_SiftingDate").Delete
ActiveWorkbook.Names("Warehouse_SiftingOutcome").Delete
ActiveWorkbook.Names("Warehouse_SiftingRegrets").Delete
ActiveWorkbook.Names("Warehouse_TSAgent").Delete
ActiveWorkbook.Names("Warehouse_TSBookingStatus").Delete
ActiveWorkbook.Names("Warehouse_TSDate").Delete
ActiveWorkbook.Names("Warehouse_TSOutcome").Delete
ActiveWorkbook.Names("Warehouse_TSRegrets").Delete
ActiveWorkbook.Names("Warehouse_TSWithdrawalReason").Delete
Range("'Warehouse!$Y$3:$Y$1500").Name = "Warehouse_ACAgent"
Range("'Warehouse!$AD$3:$AD$1500").Name = "Warehouse_ACBookingStatus"
Range("'Warehouse!$AE$3:$AE$1500").Name = "Warehouse_ACDate"
Range("'Warehouse!$AG$3:$AG$1500").Name = "Warehouse_ACOutcome"
Range("'Warehouse!$AH$3:$AH$1500").Name = "Warehouse_ACWithdrawalReason"
Range("'Warehouse!$D$3:$D$1500").Name = "Warehouse_ApplicationDate"
Range("'Warehouse!$E$3:$E$1500").Name = "Warehouse_ApplicationMethod"
Range("'Warehouse!$A$3:$A$1500").Name = "Warehouse_CandidateName"
Range("'Warehouse!$F$3:$F$1500").Name = "Warehouse_Origin"
Range("'Warehouse!$G$3:$G$1500").Name = "Warehouse_Reserves"
Range("'Warehouse!$H$3:$H$1500").Name = "Warehouse_ScreeningAgent"
Range("'Warehouse!$J$3:$J$1500").Name = "Warehouse_SiftingDate"
Range("'Warehouse!$I$3:$I$1500").Name = "Warehouse_SiftingOutcome"
Range("'Warehouse!$K$3:$K$1500").Name = "Warehouse_SiftingRegrets"
Range("'Warehouse!$M$3:$M$1500").Name = "Warehouse_TSAgent"
Range("'Warehouse!$R$3:$R$1500").Name = "Warehouse_TSBookingStatus"
Range("'Warehouse!$S$3:$S$1500").Name = "Warehouse_TSDate"
Range("'Warehouse!$U$3:$U$1500").Name = "Warehouse_TSOutcome"
Range("'Warehouse!$W$3:$W$1500").Name = "Warehouse_TSRegrets"
Range("'Warehouse!$V$3:$V$1500").Name = "Warehouse_TSWithdrawalReason"

The problem I am having is that on a secondary sheet within the workbook I have a number of tables filled with formulas referencing these named ranges. After the code runs and the named ranges are redefined, all of the formulas change to #REF! and the automatic counting is lost.

Is there a better way of doing what I am trying to achieve, or am I just doing something wrong?