Relative Selection

[Pepper_]

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.

[Pauleyb]

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).

[TMS]

Untested, but try:

Please Login or Register  to view this content.

[Pepper_]

TMS,

This just makes the S column equal to the R column. I need to incorporate the R reference into the equation. For example

S8=(Date - Worksheets("MAIN SLATE").Range("R8")) / 30.14
S10=(Date - Worksheets("MAIN SLATE").Range("R10")) / 30.14
S12=(Date - Worksheets("MAIN SLATE").Range("R12")) / 30.14

So on and so forth.

[Pepper_]

Read a book about VBA and found how useful the R1C1 is:

Please Login or Register  to view this content.