How to copy worksheet, adjust values, then populate new worksheet?

Question

I have a macro that produces a dynamic number of new workbooks by copying a worksheet in the original workbook.

In Step-Through mode, values in the copied worksheet are correctly adjusted in the loop based on two inputs and are populated in the new worksheets.
When run normally, all new workbooks contain the original values.

I understand using .Select is bad. I don't know how to work around this.

Sub create_jvs()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim achWS, refWS As Worksheet
    Dim refRng, strtCell As Range
    Dim j, fRow, lRow As Integer

    Set refWS = wb.Sheets("References")
    Set achWS = wb.Sheets("ACHLinkage")

    'get first and last row of constraint range in ACHLinkage
    fRow = 2
    lRow = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, 
    LookIn:=xlValues).Row

    'loop to produce separate ach jvs
    For j = -29 To (-29 + (lRow - fRow)) Step 1
    
        ThisWorkbook.Sheets("References").Activate       'select the ref worksheet
        refWS.Range("R31").Select                        'select the first reference cell 
    
        'update formula 1
        ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j & "]C[10] = """",  """", 
        IF(ACHLinkage!R[" & j & "]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[" & j & 
        "]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[" & j & "]C[11],2)))"
    
        ActiveCell.Offset(1).Select
    
        'update formula 2
        ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 1 & "]C[10] = """", """", 
        IF(ACHLinkage!R[" & j - 1 & "]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[" & 
        j - 1 & "]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[" & j - 1 & "]C[11],2)))"
    
        ActiveCell.Offset(1).Select
    
        'update formula 3
        ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 2 & "]C[10] = """", """", 
        IF(ACHLinkage!R[" & j - 2 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[" & j - 2 
        & "]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[" & j - 2 & "]C[11],2)))"
    
        ActiveCell.Offset(1).Select
    
        'update formula 4
        ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 3 & "]C[10] = """", """", 
        IF(ACHLinkage!R[" & j - 3 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[" & j - 3 
        & "]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[" & j - 3 & "]C[11],2)))"
    
        ActiveCell.Offset(1).Select
    
        'update formula 5
        ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 4 & "]C[10] = """", """", 
        IF(ACHLinkage!R[" & j - 4 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[" & j - 4 
        & 
        "]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[" & j - 4 & "]C[11],2)))"
    
        ThisWorkbook.Sheets("ACH JV").Copy   'copy jvWS to new wb
    
    Next j

End Sub

Please read this answer on [how to avoid using select/activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Raymond Wu, Oct 25 '21 at 13:22
Yes, the formulas in ACH JV directly reference the updated references formulas. I thought it would be easier to create an actual range on the references sheet and simply place those values in the ACH JV sheet, since those values are not next to each other and cannot be a range. — inscoder, Oct 25 '21 at 15:43

score 0 · Answer 1 · answered Oct 25 '21 at 15:42

I think ACH JV is referring back to References. When you copy ACH JV, you copy the formulas too and they continue to refer back to References - they all point to the same cells on References. When you change those cells, you're changing every spawned workbook too. It seems to work when you're stepping through because you're looking at each spawned workbook right after the copy. If you were to look at the previous workbook while stepping through, it would look just like the current one.

If I'm correct, you need to turn those formulas into values after each copy. Example code below.

Sub create_jvs()

    Dim achWS, refWS As Worksheet
    Dim j As Long, lRow As Long, fRow As Long
    Dim rCell As Range
    
    Set refWS = ThisWorkbook.Sheets("References")
    Set achWS = ThisWorkbook.Sheets("ACHLinkage")
    Set rCell = refWS.Range("R31")
    
    'get first and last row of constraint range in ACHLinkage
    fRow = 2
    lRow = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    
    'loop to produce separate ach jvs
    For j = -29 To (-29 + (lRow - fRow)) Step 1
    
        rCell.FormulaR1C1 = "=IF(ACHLinkage!R[" & j & "]C[10] = """",  """", IF(ACHLinkage!R[" & j & "]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[" & j & "]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[" & j & "]C[11],2)))"
        rCell.Offset(1).FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 1 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 1 & "]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[" & j - 1 & "]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[" & j - 1 & "]C[11],2)))"
        rCell.Offset(2).FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 2 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 2 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[" & j - 2 & "]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[" & j - 2 & "]C[11],2)))"
        rCell.Offset(3).FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 3 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 3 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[" & j - 3 & "]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[" & j - 3 & "]C[11],2)))"
        rCell.Offset(4).FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 4 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 4 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[" & j - 4 & "]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[" & j - 4 & "]C[11],2)))"
    
        ThisWorkbook.Sheets("ACH JV").Copy   'copy jvWS to new wb
                
        'Turn formulas into values
        ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    Next j

End Sub

This works! I understand much more about this type of problem now...thank you so much! — inscoder, Oct 25 '21 at 15:46

How to copy worksheet, adjust values, then populate new worksheet?

1 Answers1