Forum Discussion
Solved
Save / Restore all formulas on a worksheet
Hello all. I have laid out a complex time edit sheet in excel. It pulls values from one table, the users will edit the values on this sheet and the edits will save to a second table for submitting to another database.
The cells they are editing will have formulas in them, and their edits will over-write the formula with the values the user prefers, this is fine. But when they are done, or if there is a problem and they want to start over, the formulas are gone. They would have to open a fresh copy of the excel sheet to start over.
I would like some way to save the original sheet/format, some way to reset. Maybe a VBA button that reset everything? Needs to be easy for the end user.
I did find a video using the record macro feature to get R1C1 code to write formulas to a cell. But these worksheets are huge with hundreds of cells. Getting the code that way for each cell would take days (I have over 20 worksheets I need to do this for). Looking for something easier if possible,
The macro writes one formula in 10000 cells in this example. In cell I2 the formula =B2*C2 is entered, in cell I3 formula =B3*C3 and so on. If any changes are made in column I the formulas can be re-entered by running the macro. I wanted to show that the original formulas can be re-entered in almost no time with the help of a macro.
Can you share the formulas of your sheet? Maybe the formulas can be written into the file with the R1C1 code.
- If you have access to Inquire, a quick solution is to run Workbook Analysis which will create a workbook listing all formulas.
https://support.microsoft.com/en-us/office/analyze-a-workbook-with-spreadsheet-inquire-5991e8fa-f1c1-401a-ae3f-469384ae3e3b#:~:text=%20Start%20the%20workbook%20analysis%20%201%20In,cell%20or%20range%20of%20cells%2C%20data...%20More%20 I solved something like that in a very unorthodox way. But this is probably only suitable for worksheets where there are not so many formulas. If you overwrite a formula with a value and later prefer to restore the formula.
I wrote the formula in the selection for the dropdown and did not restrict the values to those from the dropdown. You can also write your formula in the name editor and then only call up the name in the dropdown. This keeps the formula dynamic if you need it in several places in the worksheet.
I have made a very small example document. Maybe this will give you a useful idea outside of VBA. But the code is certainly great!I would like to thank everyone for the help with this issue. Leadership has dropped this request and we are moving on to other projects.
I think the best option I found for my needs was creating a backup of the worksheet in another hidden tab, and making a macro to delete this sheet, and then copy, and rename the backup sheet, and then changing a few variable fields to make the filters work correctly. If I run into this again in the future I will try that.
Thanks again!
- I have created similar spreadsheets before and the easiest solution for me is to change the attributes of the file where the end users input their data to read-only. This will keep them from making any changes and also allow more than one person to use it at the same time.
Sub test() Dim ws As Worksheet Dim lastRow As Long Dim str1 As Variant Dim str2 As Variant Range("I:I").Clear Set ws = Tabelle1 lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row str1 = ws.Range("B2:B" & lastRow).Value str2 = ws.Range("C2:C" & lastRow).Value ws.Range("I2:I" & lastRow).FormulaR1C1 = "=RC[-7]* RC[-6]" End SubMaybe this is a helpful example. In the attached file you can click the button in cell E2 to run the macro. The macro enters a R1C1 formula in column I and the runtime is less than 1 second for 10000 rows in my sheet.
I am not clear on what that macro is doing, could you explain?
Also, here is a screenshot of my worksheet, every cell visible starts with a formula in it, until the user overwrites it with changes.
Edit: OK, i see, your writing one formula to one cell correct?
unfortunately i am dealing with hundreds of cells (in the image above, this thing extends to the right for quite a ways) and the formulas are different in each cell.
I was hoping for an automated way to copy all the formulas and store them to be reapplied later with a macro.
The macro writes one formula in 10000 cells in this example. In cell I2 the formula =B2*C2 is entered, in cell I3 formula =B3*C3 and so on. If any changes are made in column I the formulas can be re-entered by running the macro. I wanted to show that the original formulas can be re-entered in almost no time with the help of a macro.
Can you share the formulas of your sheet? Maybe the formulas can be written into the file with the R1C1 code.
