Forum Discussion
How to' Close Off' a month of data and store values for propserity
Can anyone help me with a Macro Button or Command of some sort to achieve the following.
PS: It's not actually being used on this example sheet, it's for a Project Management resource & cost tracking sheet - so advice to simply select the range manually, then use Paste Special (Values) while appreciated, isn't what I am looking for. The sheet I need to use this in is very complex and I need to perform the action on dozens of columns on several tabs all at the same time, and to ensure none are missed. You can't trust PM's to get that right every time!
So onto my simply example, a simple monthly Sales Tracker.
In the above example, the Market Price is multiplied by Units Sold to get the monthly sales figure for January, with a simple formula in Cell E4 of (=SUMC4*D4). For February, Cell G4 is simply C4*F4). Simple right!
The problem is this; In February, the Market Price changes. February's sales will need to be multiplied by the new Market Price but when I update the Market Price, the January Revenue figure is also updated, but I need it to remain as it was.
I am looking for a simple Macro command to do this following:
- Auto selects the a range of columns based where D2 = C2
- Noting that next Month it won't be D2 = C2, rather it will be F2 = C2 (February)
- Convert all Past Linked Formulas to their Values in that range
- Hides the selected columns, so that Column F & G are now displayed immediately following Colum C
- If you could also assume other columns in another Tab (let's say Columns "D & E' on "TAB2") also need the same actions, so I can see how I apply the Macro across multiple ranges that would be awesome.
Thanks all
Kane (from Australia)
Hi Kane from Australia,
If you like, you can test my attached file. In cell C2 you will find a dropdown. Only the month you enter here will be updated.
I hope this comes pretty close to what you want to achieve.
See the attached workbook. For security reasons, I moved the VBA code from the VBA Editor into a separate worksheet; you can easily review the code before installing it (to a standard code module), save the result to a macro-enabled workbook, and test it.
If you are planning to allow PMs to modify a macro-enabled worksheet, you have to consider security issues. (Don't discount the possibility that you may have a disgruntled PM in the future, or sophisticated invasive malware.) You probably need not be concerned about users viewing the code, but it needs to be protected from unauthorized changes.
You might apply VBA project security (but make sure any passwords are stored securely and are accessible to the company even if you get run over by a bus/kangaroo).- a Stack Overflow discussion
- further discussion from a software company
- notes from the Australian government
And you want to make the resulting workbook "trusted" (such as placing it in a trusted directory), so users do not get inappropriately habituated to click "Enable macros" whenever such a warning appears.
It is possible to separate the code and the data into separate workbooks, but I'd rather take a nap than think about that.
