Named range prob using VB to move wksheet to an existing wkbook

[Guest]

Is there a way to delete all named ranges in a workbook? I have tried

ActiveWorkbook.Names ("*").Delete

but the wildcard does not work in this code.

The background is that I have to replace worksheets each month in workbooks
for 9 countries. I have a macro set up that deletes the prior month
worksheets from each country file and then opens the various workbooks with
the current month's info and copies the appropriate worksheets into the
country's file. The problem is that the worksheets I am copying in
apparently have a named range and I receive the message regarding the fact
that the name already exists on the destination worksheet which causes my
macro to fail.

I set up the original country workbooks myself and they do not require any
named ranges. I think that it is a leftover name from the company that
prepares the monthly reports, but by the time I get it, it doesn't have any
formulas.

 

[green biro]

Is there a way to delete all named ranges in a workbook?

Sub blahblaah()

With ActiveWorkbook

For x = .Names.Count To 1 Step -1

.Names(x).Delete

Next

End With

End Sub



Regards



GB

 

[Bob Phillips]

Sub deleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)