transfer names to new workbook (excel 97)

Topic

New Reply

I have a workbook that is filling data daily, for some period of time the file size become huge. I then create a new workbook and copy, pastespecail vaule only from the old one to the new one. That will reduce the file size a lot. The problem is how to transfer (tried using copy, not work)the defined name from the old one to new one easily?
thank you for any help!

Reply | Quote

Replies

Try copying a sheet to a new workbook. Right click of the sheet you prefer to copy, select Move or Copy. In the “To Book” section select new book BE SURE TO CHECK create a copy otherwise the entire sheet will be moved to the new file. You can then cleanup the sheet in the new workbook for data you do not want to retain.

John

Reply | Quote

In Explorer, I would make a copy of the existing workbook, then edit the new workbook to remove all of the data that you no longer need. I always select all of the rows that I don’t need, plus the empty ones at the bottom, and then delete them. Clearing them is not enough. Do the same for columns at the right. This will reset the UsedRange and should help your file size. –Sam

Reply | Quote

I had tried both, the minimun size I could get is 2.5M. and by creating new workbook and copy paste value, I could reduce the size to 500k. Only problem is that I need to move my “names(created by insert, name, define)” to the new one easily.

Reply | Quote

I have the same problem and came up with a (sort of) solution: Automating the naming inside a pre-selected range.

However this requires you to
a) Write the cell names in a separate column
If you have a multi-column tables, to use an appendix/index to differenciate the names (no, I do not use the ‘Natural Language’)

It is semi-automatic only, but OTOH it surely helps when developing the workbook. If interested, ask.

Reply | Quote

– Download my name manager from the excel MVP page below.
– Install it
– open your source file
– Open the name manager (Tools, Name manager)
– Click the List button
– You now have a sheet with a listing of all names in your workbook
– copy (paste values if you like) all sheets to a new workbook, including the newly added one.
– Select all names you want included in your new workbook in the sheet called something like “Names 2002-nov-14”
– start the name manager again and click the button called “pick up”.
– you can now safely delete the names sheet.

Reply | Quote

Hi Pieterse
try your namemanger and when select the names in the new workbook, can load the namemanager, it says “no name in active workbook” , any idea?
thanks
Joe

Reply | Quote

Oops, hadn’t thought of that!

To overcome this, simply define a single name in the new workbook (Insert, name, define). then Start the name manager.

Reply | Quote

encounter other problem.
when press pick up, got message” failed to (re)define this name: a error:application-defined or object-defined error”.

Reply | Quote

Ouch, could you attach the file with the names list to a message (Zipped)?

Reply | Quote