Never save Sheet1 of my workbook. Or some other solution to my problem?


Marvin Hlavac

Happy New Year to all very helpful people in this NewsGroup.

Could someone please help me with this one... What I need is:

Everytime I open my workbook I want in an unprotected cell A1 to have a
formula. As an example let us use formula =SUM(1,1)

Again, that cell is unprotected and I would like to be able to enter to that
cell anything but when the workbook is saved, closed and reopened I would
like in cell A1 to have again the formula even when there was something else
before the workbook was saved.

If that is difficult so achieve, would it perhaps be possible to set it up
in such a way that Sheet1 of this workbook would never be saved when I save
my workbook?

Marvin Hlavac

Why not have a workbook_open event that places the formula?

Then it *is* possible. Great!!! Would you help me with that Don? I promise I
will start learning a little bit more about all this in 2004 ;-)

John Wilson


Go to the VBA Editor (Alt + F11)
Double click on the "ThisWorkbook" module in the
VBA Project Window.
Copy the following code in the code panel on the right:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1").Formula = "=Sum(1,1)"
End Sub


Marvin Hlavac

John & Don,

You are the greatest! It works, it does exactly what I needed! Thanks both a
Apr 20, 2017
Reaction score
Hi John,

I just found your thread. I am trying to do a similar thing to Marvin.
The only difference is I would like to keep the formula of all the range : A1:A99 rather than just A1, upon closing the spreadsheet.

Also, the formula in: A1 is =IF(A2="","",L2)
and the formula in A2 is =IF(A3="","",L3)
and the formula in A3 is = IF (A4="","",L4)
and so the pattern goes on.

Could you please help me get the correct VBA code?

Thanks heaps
Omar Hanouni
Sydney, Australia

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question