Named Range Issue

[MikeDBMan]

I have VBA code that opens a file, deletes a sheet called "Parameters" and then does this:

    Sheets("Parameters").Select
    ActiveWindow.SelectedSheets.Delete
'Now delete all the named ranges because they will reference errors
    ActiveWorkbook.Names("Fee").Delete
    ActiveWorkbook.Names("BillingEmail").Delete
    ActiveWorkbook.Names("BillingEmail2").Delete
    ActiveWorkbook.Names("BillingPhone").Delete
    ActiveWorkbook.Names("BillingPhone2").Delete
 
'Then it goes to the "master" file and gets the new paramters sheet and
'moves it to the open file (above) that had the parameters sheet removed
 
    Windows("Projects.xlsm").Activate
    Sheets("Parameters").Select
    Sheets("Parameters").Copy Before:=Workbooks(ThisBook).Sheets(1)
'ThisBook is the variable for the first file above.

The problem is that when I am in the first workbook the named ranges are now referenced as [Projects.xlsm]Parameters!$b$38, etc. Previously they were just Parameters!$B$38, etc. Any suggestions on how to get them to "copy over" successfully. Note: the scope on them is needed to always be Workbook because these parameters are used in many places throughout each workbook they appear in.

 

[shg]

Maybe instead of deleting the sheet, you could copy the contents of the new sheet into it, preserving the names.

 

[MikeDBMan]

That is certainly possible. But what is weird is that when using Excel 2010 it worked fine but now I have Excel 2007 and I have this issue. I will wait until tomorrow to see if someone has a better idea and then use your idea, which will certainly work just fine. Thanks for suggesting it. Sometimes I can't see the forest for the trees.

 

[shg]

That would be very nice behavior if it works as you say in 2010, caching the invalid names and fixing them when a worksheet reappears.