none
Worksheet Copy and Restore RRS feed

  • Question

  • I am trying to create a worksheet copy and restore facility as illustrated in the following code:

    Sub Copy()
        Workbooks("TestA.xlsm").Sheets("fred").Copy After:=Workbooks("TestC.xlsm").Sheets("Sheet1")
        Workbooks("TestA.xlsm").Sheets("Mary").Copy After:=Workbooks("TestC.xlsm").Sheets("Fred")
    End Sub

    Sub Restore()
        Workbooks("TestA.xlsm").Sheets("Mary").Delete
        Workbooks("TestC.xlsm").Sheets("Mary").Copy After:=Workbooks("TestA.xlsm").Sheets("fred")
        Workbooks("TestA.xlsm").Sheets("fred").Delete
        Workbooks("TestC.xlsm").Sheets("fred").Copy After:=Workbooks("TestA.xlsm").Sheets("Sheet1")
    End Sub

    Where cell A1 in Sheet fred reference cell B2 in sheet mary

      A1 = mary!B2

    The sheets fred and mary in TestA are copied to TestC and then restored by copying back from TestC to TestA

    However I run into a problem when I have to delete the original fred and mary in TestA before restoring the saved sheets from WorkBook TestC.  The reference in fred to mary!B2 gets screwed up.

    Does anyone know of a way around this problem?  Some way of making the sheet reference absolute perhaps?

    Tuesday, June 11, 2013 8:46 AM

Answers

  • In your macros, you could convert every formula on every sheet into a string, move the worksheets, then change all the formulas back. For example, add this and run test. (Note that this code does not work if you have array formulas of any type - but that can be added)

    Sub test()
        Dim w As Worksheet
        For Each w In Workbooks("TestA.xlsm").Worksheets
            SAFormulaToText w.Cells
        Next w

        For Each w In Workbooks("TestC.xlsm").Worksheets
            SAFormulaToText w.Cells
        Next w
        
        Restore 'Replace worksheets with code here

        For Each w In Workbooks("TestA.xlsm").Worksheets
            SATextToFormula w.Cells
        Next w
        
        For Each w In Workbooks("TestC.xlsm").Worksheets
            SATextToFormula w.Cells
        Next w

    End Sub


    Sub SAFormulaToText(rngF As Range)
        Dim myCell As Range
        
        On Error Resume Next

        For Each myCell In rngF.SpecialCells(xlCellTypeFormulas)
            myCell.Formula = "'" & myCell.Formula
        Next myCell

    End Sub

    Sub SATextToFormula(rngT As Range)
        Dim myCell As Range

        On Error Resume Next

        For Each myCell In rngT.SpecialCells(xlCellTypeConstants, xlTextValues)
            If Left(myCell.Formula, 1) = "=" Then myCell.Formula = myCell.Text
        Next myCell

    End Sub



    Tuesday, June 11, 2013 2:51 PM

All replies

  • In your macros, you could convert every formula on every sheet into a string, move the worksheets, then change all the formulas back. For example, add this and run test. (Note that this code does not work if you have array formulas of any type - but that can be added)

    Sub test()
        Dim w As Worksheet
        For Each w In Workbooks("TestA.xlsm").Worksheets
            SAFormulaToText w.Cells
        Next w

        For Each w In Workbooks("TestC.xlsm").Worksheets
            SAFormulaToText w.Cells
        Next w
        
        Restore 'Replace worksheets with code here

        For Each w In Workbooks("TestA.xlsm").Worksheets
            SATextToFormula w.Cells
        Next w
        
        For Each w In Workbooks("TestC.xlsm").Worksheets
            SATextToFormula w.Cells
        Next w

    End Sub


    Sub SAFormulaToText(rngF As Range)
        Dim myCell As Range
        
        On Error Resume Next

        For Each myCell In rngF.SpecialCells(xlCellTypeFormulas)
            myCell.Formula = "'" & myCell.Formula
        Next myCell

    End Sub

    Sub SATextToFormula(rngT As Range)
        Dim myCell As Range

        On Error Resume Next

        For Each myCell In rngT.SpecialCells(xlCellTypeConstants, xlTextValues)
            If Left(myCell.Formula, 1) = "=" Then myCell.Formula = myCell.Text
        Next myCell

    End Sub



    Tuesday, June 11, 2013 2:51 PM
  • Thanks Bernie,

    This certainly works. I am however a little concerned with the ultimate execution time. I am working on a large application which involves logical transactions that update a number of large sheets with complex cell formulae and a database and hence I need to take before images with virtually all transactions. I might have to revisit this later on when the application is further developed. However in the meantime I can progress. Thanks.

    Tuesday, June 11, 2013 10:12 PM