copying sheet : prevent to copy all Names of entire workbook

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I'm copying a sheet (with three charts : in case it matters) to a new workbook.
On this sheet are about 5 Named Ranges. When I click at the top left to see names (don't know the word in English) those 5 names are displayed.
But opening the Menu "Insert" (if not correct English, it's the 4th menu), Names, Define: I see all the names (1340) of the "source"workbook adding some unneeded 400kb.
At the end of the procedure, names are removed, taking more than half a minute since Excel doesn't provide a function: "clear all names".
HOW CAN WE PREVENT TO COPY ALL THOSE NAMES ?

thanks for suggestions,
Erik

Code:
Sub compile_graph()
Workbooks(file_name).Activate
Sheets("graph").Copy before:=Workbooks(new_file_name).Worksheets(1)
Sheets("graph").Unprotect "abcd"
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
End Sub

Sub delete_names()
For Each nnn In Workbooks(new_file_name).Names: nnn.Delete: Next
End Sub
[/img]
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
Re: copying sheet : prevent to copy all Names of entire work

Two things to try.

1. Instead of copying the sheet create a new one and copy the data and charts to it.

2. Try turning off screen updating while you are deleting the ranges. I don't see why it sould take that long to delete the ranges and mybe this will help.


Good luck...Mike
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
It taking you 30 seconds to run that last bit of code. To just delete 5 names? That doesn't make much sense to me. Maybe try to delete just from the worksheet names.
Code:
Sub delete_names() 
    For Each nnn In Workbooks(new_file_name).Sheets("Graph").Names
        nnn.Delete
    Next 
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Re: copying sheet : prevent to copy all Names of entire work

DRJ & mjbeam,
Thanks a lot for quick reply.

to DRJ : 30 seconds not for the 5 names but for the 1340

mjbeam,
The solution was not the screenupdating but you invited me to think. An item that we sometimes forget : Application.Calculation = xlManual

Code:
Application.Calculation = xlManual
For Each nnn In Workbooks(new_file_name).Names: nnn.Delete: Next
Application.Calculation = xlAutomatic

This did the job in about 3 seconds.

Your other solution to copy charts can not be done I think, because I don't want to unprotect that sheet during the procedure.

Great to find solutions together ! Thank you both !
Erik
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

You can unprotect the sheet during the procedure and to stop people from stopping the code mid way (and thus leaving your sheet unprotected) add this line.
Code:
Application.EnableCancelKey = xlDisabled
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Re: copying sheet : prevent to copy all Names of entire work

WONDERFULL

We get answers without really putting the questions here !
Thank you very much!

(y)

Erik
(going to sleep : here it's 02:00 am ! :oops: )
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top