VBA to Copy/Paste Formula Into Next Cell (Column)

[Gerwe1988]

Hello everyone,

I'm new to macros and need some assistance with establishing a code for my forecast file. The macro will scan row 3 for the current "reporting" month, to decide which column to update (AS). The macro will then copy & paste the prior months formula from one cell to next, into the current "reporting" month. The prior months formula will need to be hard coded. There are certain rows that need to be updated in this process (Rows 16,17, 27, 28). For example, row 16 "New patients", the prior months formula (Jan-22, AR16), will be copied and pasted into the next column (AS16). The prior months formula (AR16) will be hard coded. Thanks in advance!

 

[airforceone]

Sorry, each tab/worksheet has formulas in different columns and rows. Could you provide an example on how the macro above would be modified to accompany different range/columns? The "reporting" identifier is static and on row 3 on all tabs.

are all the formulas your talking in various sheets won't change? if so, would it possible to make an inventory of all your sheet and note on what row and/or columns address of your "formulas"?
that way we can encapsulate the code with an "if sheetname = sheet1_Set_A" statement.
Let say if condition are met based on your noted cell address for each sheet you can then make a variable that correspond to that sheet name with "cell address" different from the other sheet!

ie. if sheetname = employee then
use the code with range address for employee sheet (A1:B100)
if sheetname = salary then
use the code range address for address sheet (B101:C100) and so on based on your different sheet!

if that's not possible or if you can't do that then you do it the hard way! create multiple instance of the code for different sheet