"Freeze" the results in a table with a formula

Hello Petersburg1,

Could you please post a file which contains sample data to illustrate what you need?

Regards,

Long

Dear Petersburg,

Would it help that with the save event you automaticaly copy and paste special values..

It could be made that the file is ready for A new month, or you have to click a button to refill formula's to where you need them?

Does any of this make sence?

KR

Eric

Dear longtruong and Zwiekhorst.
I have attached the file. Hope it will help to find a solution.
Currently I have data from Feb in the import sheet and got the results. I need now to "freeze" the results even I delete the import data and get data for march in.
thanks

Paper-Usage-Tracking-NIK.zip

Dear petersburg

This file holds a macro that will replace the formula in the printer sheet with its value when you save the workbook.

Only where the SUM > 0

if you need to do the same in other sheets to , the macro is easily adapted..
If you don't know how just ask me..

Kind regards

Eric
Paper-Usage-Tracking-NIK.xls

Hi Eric,
Yes, I would need it for more sheets also in other files so I have to understand the code a bit better and I will be able to adapt it myself:
1. Where and how to add more sheets?
2. For i = 2 To 13 is the range right in row 4 right?
3. For j = 5 To 20 is the range of rows with the formula, right?

However, something seems not to work together with my already existing function....
It seems to be connected with the fact that I moved the Data Import sheet from the first position to the last position. Why is that? What need to be changed in the code where that previous and new function will work smoothly together?
And how to reset everything? I mean to loose data but have for each month calculation again active?
At the end of the year I would need it and also in between when I get a data update of a certain month based on some mistakes...there should be the possibility to "release" a frozen month and have new data...Maybe with a help of a helping line on top of each month? By default = C for calculate and f= for frozen. As soon I place a a c instead of an f and save, next time I open I will have lost the data but therefore can calculate again?
thanks
Nils



Sub valpst()
Dim i, j As Integer
Dim totl As Variant
Sheets("Printer-Report").Select
For i = 2 To 13
    If Cells(4, i) > 0 Then
        For j = 5 To 20
            Cells(j, i) = Cells(j, i).Value
        Next
    End If
Next
       
i = i
End Sub

I increased the points...

Dear Petersburg,

I didn't find the time to look at your responds in the weekend.. Will get to it now.

I wil try to answer your questions

1 : The macro now looks for a sheet named "Printer-Report" if there exists no sheet with that name it will give a error. If you want to use different sheetnames you can put them into variables and use the variable instead of the "Printer-Report")
2: Indeed it questions Cells(4,I) where the 4 stands for the row. it look for a sum that is > 0 so it knows there are values in the column.
3: and again you are right the 5 to 20 are the row numbers of wich the formula is to be transformed into a value.

The place for data import sheet is not important, I assumed you have always the Printer-Report to start from.

on how to reset averything, there are several possibilitys:

1: you could have a template somewhere with the page holding all formulas, then at years end delete sheet without formulas and copy in the sheet with formula's

2: make a macro that populates the fields with the formula..

your suggestion to put C and F are acceptable to, what would you say about putting a 0 in the sum would reset calculation?

I could help with this if you would need it just ask...
Kind regards

Eric

Dear Petersburg,

I altered the code so that when saving the file it checks if the sum was zero'd that means the formula is gone a a real 0 was taped into that cell.
Then it will repopulate the column with the formula, even the zero'd sum..


Hope this and the above explanation helps you ..


Kind regads

Eric
Paper-Usage-Tracking-NIK.xls

Hi Eric,
nice with the zero and your macro does work!
However I does not work in combination with my macro?!
1. When I open the file I get the message as before, asking if I want to delete the range at sheet1. Even I confirm nothing is deleted
2.If I press the "delete button" on that sheet it also does not work. It is as I would be in design mode. If I click to the delete button it reacts like in design mode and shows the button frame.
3. On the original excel file I have sent to you it also does not work but it works as soon you move the data import sheet back to the first position. Therefore my question:

A) Could you make both function work together?
B) Why "my marco" requires that the data import sheet is the first sheet? What to change where to have it any other place?
C) Why the button does not work as well?
D) Could you make your macro working for all three report sheets?

Thank you very much in advance for your help and patience with me.
Nils

Hi Petersburg,
 A i will do this but I can send it this evening..

B     If CheckMe = vbYes Then ThisWorkbook.Sheets(1).Range(rng1.Address).ClearContents
 Change Sheet(1) to Sheets("Data-Import")  thisway it looks for the name and not for the internal number..

c: the button doed not work because it can not find the right sheet.

D: yes I could, will send the result this evening..

Eric,
I tried a bit but it does not work either this:
If CheckMe = vbYes Then ThisWorkbook("Data-Import").Range(rng1.Address).ClearContents

nor that way and some other variants...

If CheckMe = vbYes Then ThisWorkbook.("Data-Import").Range(rng1.Address).ClearContents

I get "Object doesn't support this property or method" (runtime error 438) back.
thanks
Nils

Dear Petersburg,

Try this version.
Your problem lies in the fact you want to do it in the workbook open macro.
I placed it in the workbook modules and now it works.

I changed the macro so it works with all the other sheets, had to add sum to one though.
Paper-Usage-Tracking-NIK.xls

Hi, sorry for late answer,,,,still had no time to check...will answer as soon as possible. Did not forget about you!
thanks
nils

Dear Zwiekhorst:
I'm back from sick and vacation and found now time to finalize this task. I had a look to the file, understood how to fix the issue I had with the button to delete content too.

When I now copy new data to that file I get as result perfectly calculated as I need on the report sheets and they come without formula, only values. Actually all I need. In this way I can delete and copy next month and still keep the data from the previous month.

To recover the formula, I can actually just copy e.g. April back to March and so on....but I remember that you implemented something with the zero....however this seems not to work or I do or don't do something which is required to trigger that function?

I have attached a pdf file with screenshots for better understanding. E.g. I have deleted the code which was still in the sheet1 for the button and I have deleted a first empty row in the module1 which makes not different to the functionality right?

More question:
Code in the sheet does only work for that sheet? Code in the module is working via macro and/or button? Code in "this workbook" works for all sheets in that workbook? Right?

What I have to add/change if I want to add that functionality to one or several more report sheets in the near future? Could you send me just the code block I would have to add to the "this workbook"?
thanks
Nils












Excelfilescreenshots.pdf

Dear Eric,
thanks so much. This task with all my questions is already worse more then 500 points I guess.
Ready to give to you if possible,...

Your answer was very helpful, as it helps me to understand important basics. However still not clear is for me the function of the last version from you:
Is there a function, macro which does recover the formula or has this to be done manually? I'm asking because this was under discussion by setting a zero?! However this does not work or I do not know how to make it working....on the other side we can skip it for now...just let me know!

Hi Petersburg,

the reset to formula should work. Just zero the sum in the column head. If you then save and reopen the formul's are back in place.


Kind regards

Eric

Dear Eric,
PERFECT. In terms of patience, work effort and time, this is worse more then 500 points! Whenever you will come to St. Petersburg, Russia, I will give you a special discount in our B&B :-) www.rentroom.ru
Best regards
Nils

Lot's of patience, very helpful, nice solution, very happy!!

Dear Nils I will try to remember this if I will Travel to St Peterburg one day...


Thanks for the grade and the points..

Eric