Delete all Named Ranges in another workbook

**tejay** · 11-09-2015, 08:42 PM

Hi All,

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

Please Login or Register  to view this content.

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;

Please Login or Register  to view this content.

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

**alansidman** · 11-09-2015, 08:52 PM

Look at this link.

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

**mehmetcik** · 11-09-2015, 08:58 PM

Please Login or Register  to view this content.

**tejay** · 11-09-2015, 09:30 PM

Hi all,

Thanks for the ideas, I tried both suggestions - alansidman last.

On both methods, VBA gets caught here with the same error message as my original post;

Capture.PNG

Any further ideas?

Thanks

**alansidman** · 11-10-2015, 12:07 AM

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

**tejay** · 11-10-2015, 04:29 AM

Post duplicated, see below.

**tejay** · 11-10-2015, 04:34 AM

Hi Again,

No this is not the case.

To test, I deleted out all the name ranges and just added one;
- "Deleteme" with a reference to a single cell in the workbook.

See the example below;
Example.xlsm

Looks like some silly excel glitch...

Thanks

**tejay** · 11-10-2015, 05:09 AM

Grrr, when I download that spreadsheet it actually works for me even though it didn't before saving it.

It seems to be something to do with the content of my workbook.

If I delete all names and add a fresh one "test" I get this series of errors;

Attachment 429446
Attachment 429447
Attachment 429448

However if I delete all content from my workbook AND close it (saving isn't enough).
Than when I open it again, the code will work.

Why would content within my workbook be effecting this code?

**tejay** · 11-10-2015, 05:11 AM

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

Please Login or Register  to view this content.

**alansidman** · 11-10-2015, 09:55 AM

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.

**tejay** · 11-15-2015, 03:43 AM

Just to finalise this thread, it was something silly within my workbook - never found out what.
Just had to delete each named range one by one using vba.

Delete all Named Ranges in another workbook

LinkBack

Thread Tools

Rate This Thread

Display

Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Re: Delete all Named Ranges in another workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to Delete Named Ranges in A specific Sheet

Duplicate Named Ranges, how to specify which one to delete

Delete named ranges from selection

delete a bunch of named ranges

How do I delete all named ranges in a sheet at one time?

[SOLVED] How do I delete all named ranges in a sheet at one time?

[SOLVED] How do I delete all named ranges in a sheet at one time?

How do I delete all named ranges in a sheet at one time?

Bookmarks

Bookmarks

Posting Permissions