Delete all Named Ranges in another workbook

Hi All,

I have a VBA code that creates another workbook and copies a sheet to that workbook;

' Create New Workbook
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets(MySheetName).Copy Before:=wb.Sheets(1)

When copying the sheet, all the named ranges associated with it copy over as well.

I want to delete all named ranges - in the new workbook only - , this is the code I thought would work;

Dim MyName As Name
For Each MyName In Names
Workbooks("Book1").Names(MyName.Name).Delete
Next

However it doesn't do the trick;

Run-time error 1004
The name that you have entered is not valid.
Capture.PNG

However the name of the spreadsheet created is "Book1", any ideas?

Thanks

Look at this link.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=219

Sub Macro1()
    

'Create New Workbook and copy sheet one to it

    Sheets("Sheet1").Copy
   

'Delete all names in the new workbook
    For Each Name In ActiveWorkbook.Names
    Name.Delete
    Next
    
End Sub

Do any of your range names contain any of the issues referred to in your error message?

Post duplicated, see below.

I can however delete named ranges one at a time via;

Sub deleterange()
On Error Resume Next
ThisWorkbook.Names("EPS_Data").Delete
ThisWorkbook.Names("Date_Axis").Delete
End Sub

I have tested the codes provided for removing names. They work for me. I am unable to reproduce your error message. Suggest you upload your workbook for analysis.