Delete all Named Ranges in another workbook

[tejay]

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