vba loop to adjust formulas

[inscoder]

Hello,

Fairly new to VBA and working on a macro that will update 5 cells with values calculated through a formula given a dynamic range of values.

For example, the input range the formula cells would draw from on worksheet achLinkage could be AB2:AC5, or it could be AB2:AC10. I've developed this code to trap this range (there are formula blanks under, so I've had to modify it slightly to only trap actual values):

Dim rngBegin, rng As Range
Dim wb As Workbook: Set wb = ThisWorkbook
Dim achWS As Worksheet

Set achWS = wb.Sheets("ACHLinkage")
Set rngBegin = achWS.Range("AB2")
lRow = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
lCol = rngBegin.SpecialCells(xlCellTypeLastCell).Column
Set rng = Range(Cells(2, 28), Cells(lRow, lCol))

As of right now, there are 5 values (all with different formulas) on a separate sheet (call it sheet B) that use the first row of inputs from the range above. Let's say these values are located in the range R31:R35, and their respective formulas are below:

=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(References!$S$30*ACHLinkage!AC2,2),ROUNDUP(References!$S$30*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2 = "N", ROUNDDOWN(References!$S$28*ACHLinkage!AC2,2), ROUNDUP(References!$S$28*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$F$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$F$26*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$G$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$G$26*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$H$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$H$26*ACHLinkage!AC2,2)))

The ultimate goal of the macro is to use a loop to first copy sheet B to a new workbook, update these formulas in the original workbook with the next row of the dynamic table (AB3, AC3), copy the updated worksheet to a new workbook, and so on until there is an empty input row. I've done research and haven't found anything of use and am curious if I maybe need to go about this in a different way. Thanks!