Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy replace worksheet (Excel XP)

    I have 13 spreadsheets. One for each month & then a total Sheet. I am adding 1 field per sheet (1-12) on the Total sheet. Is it possible to delete one of the sheets and then replacing it without getting the formula showing the #ref error.

    I don't want to copy replace the whole area when I update the sheet but just replace the sheet with a new one same referance.

    I hope I am clear enough, any ideas would be appreciated

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy replace worksheet (Excel XP)

    Why not just copy the cells from the new sheet onto the old sheet. The formulas will not get "wiped out" since you have not deleted the references: you have only put new values in all the cells.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy replace worksheet (Excel XP)

    That won't work if the area being copied contains formulas. The formulas will refer back to the sheet they were copied form, not the sheet they are pasted to.
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy replace worksheet (Excel XP)

    I was assuming that the summary sheet contained the formulas that refered to the other 12 sheets and the other sheets were just data sheets with no formulas (except perhaps within their own sheet)

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy replace worksheet (Excel XP)

    Thanks for the advise. I don't want to go the route to copy & paste special values. I think that the best will be to put a button on the YTD sheet that will redo the formula's.

    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy replace worksheet (Excel XP)

    It might be better to use indirect formulas that contain a sheetname that is reference in a cell. Then instead of find/replace in the formulas you can just change the one cell. It might be possible to "autchange" the cell if you can create a formula to determine the sheetname based on a given criteria.

    I am not sure exactly how your formulas and sheets are setup so I can't provide any more details. Post back with more details and we can provide better instructions on how to do this (if you think this will work)

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy replace worksheet (Excel XP)

    I'm keen to see how you will use the indirect. Attached is a sample file

    Thanks

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy replace worksheet (Excel XP)

    For this example you could just use:
    =SUM('M01:M12'!B2)

    Then you can insert/delete sheets within the first and last sheets and it will always work keep the entire range. Is this what you are after? or do you really need to use individual sheets?

    Note: you could always include "dummy" first and last sheets in case you need to change any of the 12.

    If you need something else, could you elaborate, the example is not anything like I pictured the original question.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy replace worksheet (Excel XP)

    So easy. Brilliant idea

    Thanks

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy replace worksheet (Excel XP)

    <hr>That won't work if the area being copied contains formulas. The formulas will refer back to the sheet they were copied form, not the sheet they are pasted to.<hr>

    Here's a couple of macros to work around this problem if cell references don't need to be adjusted. Select the area to copy and run ApostropheInsert. After copying and pasting run ApostropheRemove while cells are still selected. Run it again on the original selection to restore formulas

    Ken

    <pre>Sub ApostropheInsert()
    Dim rngCell As Range
    For Each rngCell In Selection
    If rngCell.HasFormula Then
    rngCell.Value = "'" & rngCell.Formula
    End If
    Next
    End Sub

    Sub ApostropheRemove()
    Dim rngCell As Range
    For Each rngCell In Selection
    If Left(rngCell.Value, 1) = "=" Then
    rngCell.Formula = rngCell.Value
    End If
    Next
    End Sub
    </pre>


Posting Permissions

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