Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying Formula from corrupt workbook (2000)

    I have a large model which has a corruption in it somewhere as it will stop calculating and then crash the application soon afterwards. I want to be able to copy the formulae from the cells in this model but not just a complete copy and paste of the worksheet as this seems to carry the corruption with it. Is there a tool to copy data from corrupted workbooks or is there a quick way to copy the formulae from several thousand cells without taking any of the other hidden stuff that might come from a straight copy and paste?

    Neil
    Neil Eustice
    Woody Worshipper

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Formula from corrupt workbook (2000)

    Something like this:

    Sub CopyJustFormulas()
    Dim oSourceSheet As Worksheet
    Dim oTargetsheet As Worksheet
    Dim rCell As Range
    Set oSourceSheet = ActiveSheet
    Set oTargetsheet = Workbooks.Add.ActiveSheet
    For Each rCell In oSourceSheet.UsedRange
    oTargetsheet.Range(rCell.Address).Formula = rCell.Formula
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Formula from corrupt workbook (2000)

    Thank you very much, a neat little bit of code - I shall add it to my library of stuff I cannot do without.
    Neil Eustice
    Woody Worshipper

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Formula from corrupt workbook (2000)

    One way to get rid of corruptions might be to save-as HTML and reload the file.

    Another is to get hold of OpenOffice or Staroffice and open-save_as it with that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Formula from corrupt workbook (2000)

    Again, thanks.
    Neil Eustice
    Woody Worshipper

Posting Permissions

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