Replace a worksheet currently in use

Hi All

I have a worksheet [FRED] that is being referenced in formulas on other sheets.
I would like to replace this entire sheet with a copy of another sheet, [MARY].
I could just delete the contents of the sheet [FRED] currently in use, and then use copy and paste from the new source [MARY] sheet, but the row heights, formats etc etc are different.

In an ideal world, it would be simple to delete the sheet [FRED], then copy-and-drag the [MARY] sheet to create a new sheet, and then just rename this copied sheet as [FRED]. But if you delete [FRED], then all the formulas that currently reference that sheet will turn to #REF! errors etc.

The Following User Says Thank You to sdckapr For This Useful Post:

Running the following code should repair all your formulas in your existing sheets. I have set it up with the new sheet being placed as sheet #1. It will take the name of sheet 1, look for all instances of #REF and replace it in the formulas with the new name while it cycles through all the worksheets.

Adjust to meet your needs

HTH,
Maud

Code:

Public Sub RepairFormula()
Dim rng As Range
Dim wks As Worksheet
Dim wksname As String
Set wks = Worksheets(1)
wksname = wks.Name
'--------------------------------
For I = 2 To Worksheets.Count
Set rng = Worksheets(I).UsedRange
For Each cell In rng
cell.Formula = Replace(cell.Formula, "#REF", wksname)
Next cell
Next I
End Sub

In the existing version, the source was being copied using the Cells.Copy command.
I had to include a vba section to restore row widths and column widths etc, then had to add another bit of vba because that didn't deal with hidden rows or columns. Then, even with Application.CopyObjectsWithCells = True, shapes with properties that were defined as 'Don't move or size with cells' on the original source sheet would not be included in the ActiveSheet.Paste copy operation, and these had to have their properties set on the source sheet to 'Move but don't size with cells' to be included in the copy.

The proposed method of using the find/replace for the formula references is clever.
I believe the new process will now be a hundred times faster.
I will have to deal with re-setting the codesheet name for the subsequently-renamed-[Mary] copy to match the codesheet name of the original now-deleted-[Fred]. I'll do that tomorrow. It's bedtime here.

Once again, many thanks to you both for giving me another set of eyes on this.