copying sheet : prevent to copy all Names of entire workbook

[erik.van.geit]

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

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]

 

[mjbeam]

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]

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.

Sub delete_names() 
    For Each nnn In Workbooks(new_file_name).Sheets("Graph").Names
        nnn.Delete
    Next 
End Sub

 

[erik.van.geit]

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

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]

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.

Application.EnableCancelKey = xlDisabled

 

[erik.van.geit]

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!



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

 

[DRJ]

Your Welcome

Take Care