+ Reply to Thread
Results 1 to 5 of 5

moving sheet to new workbook, keeping values and layout, not formulas

  1. #1
    Registered User
    Join Date
    03-10-2008
    Posts
    10

    moving sheet to new workbook, keeping values and layout, not formulas

    Hi,

    Can anyone tell me how to move/copy a worksheet to a new workbook and keep the values made by the formulas but not have the formulas included on the new sheet?

    In our current method, we copy/move the sheet to a different workbook, sometimes 60 or more, then we must copy,paste special, values, in the columns that contained formulas.

    Any help would be greatly appreciated

    Mike

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Perhaps this would work, as copied sheet should be active:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-10-2008
    Posts
    10

    Unfortunately.....

    Unfotunately it doesn't work. When we right click to move or copy, we copy to a new book...Forgive my lack of technical terms, but our formulas use information on another page in the work book. when we copy the sheet to a new book we get this #REF! wherever there is a formula. And we are looking to have just the values that the formula calculated moved over. Thank you for trying, I really appreciate it....

    mike

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    OK, so you're copying the sheets manually. Could you attach a sample of your file or some of your formulae?

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Mike, it's better to reply in the thread rather than by PM.
    We have a 4 page workbook and we only manually copy one page and put it in a new workbook.

    This formula puts data from the 'Actual Results' page and places it on the sheet we copy.....

    =INDIRECT("'Actual Results'!" & ADDRESS(6+ROW()-14,INDIRECT(ADDRESS(1,2,,,"Actual Results"))-$F$8+2))

    A calculator

    =2*(SQRT((G27-C27)^2+(G55-C55)^2))

    And simple cell addition and subtraction such as:

    G32-B32

    Thanks again

    Mike
    Because you're using INDIRECT I guess the problem is that you don't have a sheet named Actual Results in the new workbook (the other formulae should work though). Can you avoid INDIRECT? I suppose you could copy the sheet in the same workbook, then paste values and then copy to a new and that could be automated using VBA.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1