Thread: Formula Disappears

Hi -- I'm hoping somebody knows why Excel is doing this -- any maybe how to fix it. I have several cells in a worksheet (my Data Sheet) that are assigned a simple formula similar to "='Other Sheet'!A1".

Some of the cells in my Data sheet lose the formula, and some do not. I have not been able to figure out why the formula goes away.

I am using a macro to reset a bunch of data in the "Other Sheet" by Clear Contents, but this happened before I put that macro into the workbook.

Thank you for replying, Tony. The spreadsheet is not my property, so I cannot share it in it's entirety. I've made a version with no labels on anything, but it does still do the same thing. in the Data sheet, B2:B7 have formulas and get cleared at random moments. It happens some other times, but it happens always when the macro ClrAll is run -- from the button "Clear All Data" on the Instructions sheet.

Hi Jlloftis, welcome to the forum.

Your code, recorded via macro recorder, is not as efficient as it could be, and any time you're switching back and forth between sheets and working with ranges, there's a chance the recorded code will work differently than expected if it's not launched from the same place/sheet.

It's always best to avoid selecting sheets or ranges, which makes for less confusing - and usually much faster - code. The updated ClrAll code below should work for you, and it doesn't clear any data from the "Data" worksheet. You have several other macros though that do similar tasks, and could be culprits. Take a look at this example, and try to update your other code to follow a similar pattern.

Also, delete any unused modules or userforms from your workbook just to help keep the filesize down.

Code:

Sub ClrAll()
Sheets("Enter Pay").Range("C8, E12, E15").ClearContents
Sheets("Enter Taxes").Range("D9, D11").ClearContents
With Sheets("Data")
    .Range("D8").Copy Sheets("Enter Taxes").Range("D13")
    .Range("D10").Copy Sheets("%").Range("E10")
End With
Application.CutCopyMode = False
Sheets("Marketing").Range("C5:E14, C17:E24, C27:C34, C38:E39, C42:E43, C46:E47, C50:E51, C54:E55").ClearContents
Sheets("Exp Detail").Range("B4:C13, C16:C30, B31:C36, B40:C47, B50:C57, B60:C67, B70:C77, B80:C87").ClearContents
Sheets("Exp Categories").Range("B11:B15, D8:D15").ClearContents
Sheets("Data").Range("D50:D52").Copy Sheets("Exp Categories").Range("D8")
Application.CutCopyMode = False
With Sheets("Exp Categories").Range("D8:D10")
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
    .NumberFormat = "0%"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    With .Font
        .Name = "Arial"
        .Size = 12
        .ColorIndex = xlAutomatic
        .Bold = True
    End With
End With
Sheets("Revenue Detail").Range("B5:D13, F5:G13, B17:D25, F17:G25, B29:D37, F29:G37, B41:D48, F41:G48, B52:D59, F52:G59").ClearContents
Sheets("Revenue Categories").Range("B7:D11").ClearContents
Sheets("Instructions").Range("J33:J34").Select
End Sub