Relative Selection

I have a function that creates a copy of the current worksheet, renames the worksheet to the current date (MMM-YY) and changes volatile functions in certain cells to static functions to effectively freeze the data or create a snapshot. The range for the data that needs to be frozen is S8:S300.

The worksheet formula is as follows:

S8=INT((TODAY()-R8)/30.14) where R8 is the Start Date for an individual. This spits out an integer value for how many months a person has been in a position.

I need my VBA sub to replace the above function with a static integer. Where S8 references R8, S10 references R10, and so on and so forth all the way to S300. Below is a copy of my "Freeze Data" sub that attempts to do this. Since I'm using a variable that does the calculation I don't know how to reference multiple ranges relative to the output ranges. I have also attached the excel sheet if any 42A's out there are lurking and want a graphical leader slate.

Please Login or Register  to view this content.

Any help would be appreciated. Apologies if this has been covered already as I am unsure of how to really describe the problem.

Check these out:
https://docs.microsoft.com/en-us/off...cel.range.copy
https://docs.microsoft.com/en-us/off...e.pastespecial

So, it seems to me that you want to first Copy the R8:R300 range and the PasteSpecial (with the xlPasteValues option).

Here is a tip. Use 'Record Macros' to auto generate the code on how to do something you do manually. You probably know how to do this with various button clicks and selections. Recording a macro will usually get you on the right path (although it is usually not the most dynamic or efficient code).

Untested, but try:

Please Login or Register  to view this content.