Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    transfer names to new workbook (excel 97)

    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!

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    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

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: transfer names to new workbook (excel 97)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    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
    [img]/forums/images/smilies/cool.gif[/img] 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.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    - 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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

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

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfer names to new workbook (excel 97)

    Ouch, could you attach the file with the names list to a message (Zipped)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •