Stop formula Auto update on 1 sheet

[afinite]

Hi guys,

I have an excel file which uses formulas to display the report's month based on system date. Unfortunately, if someone opens this file in the next month, the value changes...is there any way to stop auto update of this formula after that initial update? or stop auto update of formula on just that 1 sheet (I have other sheets too so cannot turn off formulas completely) ?

 

[amazing_wonders]

If Cell B is based off of Cell A, you could just use Copy, Paste Special, Values to remove the formula on Cell A and Cell B would be fine. You could do that manually or by VBA using an ActiveX button that would do it for you and then disappear before you send out the report. Is that a possible solution? Do the reports sent in already have the formula date in Cell A?

 

[amazing_wonders]

You could do a Copy, Paste Special, Values on Cell A and if Cell B is not reliant upon A, then you could do it on both either manually or by VBA before you sent it out. With VBA you could add an ActiveX button that you could click and have it do the process for you and then it could just disappear. Would that help?

 

[afinite]

Hello, yes I guess if there is a VBA code that can overwrite values generated by the formula in cell a and cell b as plain text then it would work great! The activex button sounds like a good idea because I can control when to overwrite the values instead of it doing it for me upon opening the file.

Could you please share the code and how to setup the activex button? I highly appreciate your help with this!

You could do a Copy, Paste Special, Values on Cell A and if Cell B is not reliant upon A, then you could do it on both either manually or by VBA before you sent it out. With VBA you could add an ActiveX button that you could click and have it do the process for you and then it could just disappear. Would that help?

 

[amazing_wonders]

Try this. Copy the code below and place it into a Module under VBAProject (PERSONAL.xlsb) in your Visual Basic Editor. Then click the MS Office logo in the upper left of your excel spreadsheet window. Go to > Customize in the left pane, then choose Macros from the "Choose commands from" drop down. Locate your macro in the list below the drop down, it will begin with PERSONAL.XLSB and then the name of the module you put the code in. (i.e.= Module1). Highlight it and click the Add button and it should appear on the right side of the window. It is now a shortcut in your quick access toolbar next to the save and undo buttons. Now when you have the spreadsheet open, just click that button in your quick access toolbar and it will remove the formulas from cells A1 & B1.

Sub CopyPasteSpecial()
    ActiveSheet.range("A1:B1").Copy
    With range(Cells(1, 1), Cells(1, 2))
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

[\code]

 

[afinite]

Hi There, I tried the code but keep getting this error:

A formula in this worksheet contains one or more invalid references.

It does not seem to be replacing the formula with text. I did Alt+f11 -> created a new module -> pasted your code and saved. When I try to save excel spits out that error.

edit: I am using office 2010 - these are the formulas I am use in the cells (if it helps):

A - =TEXT(TODAY()-DAY(TODAY()),"MMMM YYYY")

B - =TEXT(TODAY(),"mmmm yyyy")

Try this. Copy the code below and place it into a Module under VBAProject (PERSONAL.xlsb) in your Visual Basic Editor. Then click the MS Office logo in the upper left of your excel spreadsheet window. Go to > Customize in the left pane, then choose Macros from the "Choose commands from" drop down. Locate your macro in the list below the drop down, it will begin with PERSONAL.XLSB and then the name of the module you put the code in. (i.e.= Module1). Highlight it and click the Add button and it should appear on the right side of the window. It is now a shortcut in your quick access toolbar next to the save and undo buttons. Now when you have the spreadsheet open, just click that button in your quick access toolbar and it will remove the formulas from cells A1 & B1.

Sub CopyPasteSpecial()
    ActiveSheet.range("A1:B1").Copy
    With range(Cells(1, 1), Cells(1, 2))
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

[\code][/QUOTE]

 

[afinite]

Also, how would this:
ActiveSheet.range("A1:B1").Copy
With range(Cells(1, 1), Cells(1, 2))
change if I were to reference different cells - for example Cells H5 and H6 (instead of A1 and B1).

 

[afinite]

Hi again, so I was playing around and figured out a fix. Not sure if it is correct but it works!

Sub CopyPasteSpecial()
ActiveSheet.Range("H5:H6", "E9:H9").Copy
With Range("H5:H6", "E9:H9")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Seems to work great! :D Thanks for all your help

Hi There, I tried the code but keep getting this error:

A formula in this worksheet contains one or more invalid references.

It does not seem to be replacing the formula with text. I did Alt+f11 -> created a new module -> pasted your code and saved. When I try to save excel spits out that error.

edit: I am using office 2010 - these are the formulas I am use in the cells (if it helps):

A - =TEXT(TODAY()-DAY(TODAY()),"MMMM YYYY")

B - =TEXT(TODAY(),"mmmm yyyy")

 

[amazing_wonders]

That may be the issue, I am still stuck in 2007. The invalid references shouldn't have anything to do with copy and pasting. Post back the line of code that the macro breaks on if you can. In the Excel options, upper left hand corner icon, you should be able to show the Developer ribbon. You can just record the actions of copy, paste special, values and then replace that code with what I gave you.

 

[amazing_wonders]

I am glad you found your solution. The cell reference: Cells(1,1) is the same as saying "A1". The first number is the row your cell is on and the second number is the column it is in so where those two intersect, that is the cell reference.

 

[afinite]

I am glad you found your solution. The cell reference: Cells(1,1) is the same as saying "A1". The first number is the row your cell is on and the second number is the column it is in so where those two intersect, that is the cell reference.

Thanks for explaining that! I will try that next time.

Again, thanks for all your help with VBA. Amazing tool