Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts

    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.

    Any suggestions??

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    copy [Mary] to the workbook. Then use find/replace to replace all the [Fred] references with [Mary] references. Then you should be able to delete [Fred]

    Note if the name of the sheet is common and there are other things that would be replaced, you can just rename the sheet to something unique then do the Find/replace.

    If you copy the contents of the entire sheet, I thought the rows widths, column height and formatting would change with the paste...

    Steve

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

    zeddy (2014-04-19)

  4. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,291
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Zeddy,

    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

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2014-04-19)

  6. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Steve/Maudibe

    Many thanks for your suggestions. Top marks! I'll use both.

    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.

    zeddy

Posting Permissions

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