Copy replace worksheet (Excel XP)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Thanks

Reply | Quote

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

Thanks

Reply | Quote

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

Reply | Quote

So easy. Brilliant idea

Thanks

Reply | Quote

So easy. Brilliant idea

Thanks

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

[indent]

---

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.

---

[/indent]

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

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

Reply | Quote

[indent]

---

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.

---

[/indent]

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote