Excel VBA: Copying Cell Contents

Question

I have an excel program that runs a test plan for product configurations. In this program, the user clicks the "test plan" button which will then run the configuration on each product. When this happens, information from other sheets is gathered to find out the costs and totals. I have been able to gather the total costs in each column, multiply by the rates, and add each of the totals together to get the total labor costs, total material costs, total labor MTO costs, and total material MTO costs. I would much like to store these totals in another sheet, called "Totals", so I can easily monitor and manipulate them later.

However, when I try to store these values in the cells I want it will only return the most recent sheets values. (It is important to note that when the program is ran by clicking the button, old sheets with old data are deleted and replaced by newer ones. This means I cannot place any formulas within the sheets, it has to be VBA code and I do not know how many sheets will be made.) I have tried many different approaches, two of which I'll post.

    For i = 1 To 10
        wsTotals.Cells(1, i).Value = laborTotal
        wsTotals.Cells(2, i).Value = laborMTOTotal
    Next i

Here I tried to store the values I calculated into cells on the Totals sheet. But when it is ran, only the 2 most recent values are copied. (laborTotal in row 1, A through J and laborMTOTotal in row 2, A through J). However, instead of just these two values, there should be 8 for my testing since I have 4 sheets.

I have also tried:

    wsNewSheet.Range("AN1:AO1").Copy 'I stored the totals out of the way of vision to try to copy them later
    wsTotals.Select
    wsTotals.Range("A1:J2").Select
    ActiveSheet.Paste

This returned a similar result. Any help is much appreciated and I can give more information if necessary.

EDIT: I should also add that when the program is ran a new sheet is created for each smart number entered by the user on the "Test Plan" sheet. In my code, I have simply been looping through each sheet to perform the appropriate task. My problem here is that it isn't grabbing each total (or it's simply replacing the totals at the end by the most previous ones). Each new sheet is stored in "wsNewSheet".

Okay, let me try this one more time. Here is my code:

Function Test_MLFB(sMLFB, sPattern) As Boolean
Set re = New RegExp
re.Pattern = "^" & Replace(sPattern, "…", "...") & "$"
re.IgnoreCase = False
Test_MLFB = re.Test(sMLFB)
End Function

Function Test_Option_Code(sCode, sPattern) As Boolean
Set re = New RegExp
bInvert = False
If Left(sPattern, 1) = "!" Then
    sPattern = Right(sPattern, Len(sPattern) - 1)
    bInvert = True
End If
re.Pattern = "^.*" & sPattern & ".*$"
If bInvert Then Test_Option_Code = Not (re.Test(sCode)) Else Test_Option_Code = re.Test(sCode)
End Function

Function Test_MLFB_OPTS(sConfiguration, sMLFBm, sOpt1m, sOpt2m, sOpt3m, sOpt4m, sOpt5m) As Boolean
Set re = New RegExp
re.Pattern = f_Lookahead(sOpt5m) & f_Lookahead(sOpt4m) & f_Lookahead(sOpt3m) & f_Lookahead(sOpt2m) & f_Lookahead(sOpt1m) & Replace(sMLFBm, "…", "...")
Test_MLFB_OPTS = re.Test(sConfiguration)
End Function

Function f_Lookahead(sOpt_mask) As String
    If sOpt_mask = "" Then
        f_Lookahead = ""
    ElseIf Left(sOpt_mask, 1) = "!" Then 'negative lookahead assertion
        f_Lookahead = "(?!.*" & Right(sOpt_mask, Len(sOpt_mask) - 1) & ")"
    Else 'positive lookahead assertion
        f_Lookahead = "(?=.*" & sOpt_mask & ")"
    End If
End Function

Function Run_Test()
Remove_Old_Instance_BoMs
Run_Test_Case

End Function

Function Remove_Old_Instance_BoMs()

iSheetCount = ThisWorkbook.Sheets.Count
For Each aSheet In ThisWorkbook.Sheets
    If Not (aSheet.Name = "Test Plan" Or aSheet.Name = "SBoM" Or aSheet.Name = "Transformer SN" Or aSheet.Name = "Tables" Or aSheet.Name = "Phase" _
    Or aSheet.Name = "bomcost.csv" Or aSheet.Name = "bomtrafocost.csv" Or aSheet.Name = "bomhourlyrate.csv" Or aSheet.Name = "base" _
    Or aSheet.Name = "digit 9" Or aSheet.Name = "digit 14" Or aSheet.Name = "digit 15" Or aSheet.Name = "options" Or aSheet.Name = "rates" _
    Or aSheet.Name = "opt short desc" Or aSheet.Name = "Totals" Or aSheet.Name = "bomcostINFO" Or aSheet.Name = "bomcost" Or aSheet.Name = "trans") Then
        'MsgBox ThisWorkbook.Sheets(i).Name
        aSheet.Delete
    End If
Next
End Function

Function Run_Test_Case()
Dim totals As Worksheet
Dim bApplies As Boolean
Dim buildRate As Long
Dim sysBuildRate As Long
Dim testRate As Long
Dim engRate As Long
'the following are columns of the SBOM
iType = 5
iPosNum = 6 'f
iQtyNum = 7
iMatlNum = 8
iMatlDesc = 9
imlfbmask = 11
iOpt1Mask = 12
iOpt2Mask = 13
iOpt3Mask = 14
iOpt4Mask = 15
iOpt5Mask = 16
inMTOmPartDesc = 20
inMTOmCost = 21
iTotalMatCost = 22
inMTOmDTKCost = 23
iTotalLabCost = 24
inMTOmHAWACost = 25
inMTOmCurrency = 26
inMTOmBuildHrs = 27
inMTOmSysBuildHrs = 28
inMTOmTestHrs = 29
inMTOmEngHrs = 30
inMTOmIndex = 31
iMTOmPartDesc = 32
iMTOmCost = 33
iMTOmTotalMatCost = 34
iMTOmDTKCost = 35
iMTOmTotalLabCost = 36
iMTOmHAWACost = 37
iMTOmCurrency = 38
iMTOmBuildHrs = 39
iMTOmSysBuildHrs = 40
iMTOmTestHrs = 41
iMTOmEngHrs = 42

For iRow = 2 To 152 'Rows of Test Plan
    Set curTestCase = Worksheets("Test Plan").Cells(iRow, 1)
    Set curMLFBCell = Worksheets("Test Plan").Cells(iRow, 2)
    Set curoptlistcell = Worksheets("Test Plan").Cells(iRow, 3)
    Set wsSBoM = Worksheets("SBoM")
    Set wsTotals = Worksheets("Totals")
    If Not (IsEmpty(curMLFBCell)) And Not (IsEmpty(curoptlistcell)) Then
        Set wsNewSheet = Worksheets.Add
        wsNewSheet.Name = curTestCase.Value
        Worksheets("Test Plan").Rows(iRow).Copy
        wsNewSheet.Rows("1:1").Select
        wsNewSheet.Paste

        iNewSheetRow = 2
        For iSBoMRow = 4 To 1271
            bApplies = False
            If Not IsEmpty(wsSBoM.Cells(iSBoMRow, imlfbmask)) Then
                bApplies = Test_MLFB_OPTS(curMLFBCell.Value & curoptlistcell.Value, wsSBoM.Cells(iSBoMRow, imlfbmask).Value, wsSBoM.Cells(iSBoMRow, _
                iOpt1Mask).Value, wsSBoM.Cells(iSBoMRow, iOpt2Mask).Value, wsSBoM.Cells(iSBoMRow, iOpt3Mask).Value, wsSBoM.Cells(iSBoMRow, _
                iOpt4Mask).Value, wsSBoM.Cells(iSBoMRow, iOpt5Mask).Value)
            End If

            If bApplies Then
                iNewSheetRow = iNewSheetRow + 1
                wsNewSheet.Cells(iNewSheetRow, 1).Value = wsSBoM.Cells(iSBoMRow, iPosNum).Value
                wsNewSheet.Cells(iNewSheetRow, 2).Value = wsSBoM.Cells(iSBoMRow, iQtyNum).Value
                wsNewSheet.Cells(iNewSheetRow, 3).Value = wsSBoM.Cells(iSBoMRow, iMatlNum).Value
                wsNewSheet.Cells(iNewSheetRow, 4).Value = wsSBoM.Cells(iSBoMRow, iMatlDesc).Value
                wsNewSheet.Cells(iNewSheetRow, 5).Value = wsSBoM.Cells(iSBoMRow, iType).Value
                If wsSBoM.Cells(iSBoMRow, iType).Value = 1 Then
                    wsNewSheet.Cells(iNewSheetRow, 6).Value = wsSBoM.Cells(iSBoMRow, inMTOmPartDesc).Value
                    wsNewSheet.Cells(iNewSheetRow, 7).Value = wsSBoM.Cells(iSBoMRow, inMTOmCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 8).Value = wsSBoM.Cells(iSBoMRow, iTotalMatCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 9).Value = wsSBoM.Cells(iSBoMRow, inMTOmDTKCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 10).Value = wsSBoM.Cells(iSBoMRow, iTotalLabCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 11).Value = wsSBoM.Cells(iSBoMRow, inMTOmHAWACost).Value
                    wsNewSheet.Cells(iNewSheetRow, 12).Value = wsSBoM.Cells(iSBoMRow, inMTOmCurrency).Value
                    wsNewSheet.Cells(iNewSheetRow, 13).Value = wsSBoM.Cells(iSBoMRow, inMTOmBuildHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 14).Value = wsSBoM.Cells(iSBoMRow, inMTOmSysBuildHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 15).Value = wsSBoM.Cells(iSBoMRow, iMTOmTestHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 16).Value = wsSBoM.Cells(iSBoMRow, iMTOmEngHrs).Value

                    wsNewSheet.Cells(iNewSheetRow, 18).Value = wsSBoM.Cells(iSBoMRow, inMTOmIndex).Value
                    wsNewSheet.Cells(iNewSheetRow, 19).Value = wsSBoM.Cells(iSBoMRow, iMTOmPartDesc).Value
                    wsNewSheet.Cells(iNewSheetRow, 21).Value = wsSBoM.Cells(iSBoMRow, iMTOmCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 22).Value = wsSBoM.Cells(iSBoMRow, iMTOmTotalMatCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 23).Value = wsSBoM.Cells(iSBoMRow, iMTOmDTKCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 24).Value = wsSBoM.Cells(iSBoMRow, iMTOmTotalLabCost).Value
                    wsNewSheet.Cells(iNewSheetRow, 25).Value = wsSBoM.Cells(iSBoMRow, iMTOmHAWACost).Value
                    wsNewSheet.Cells(iNewSheetRow, 26).Value = wsSBoM.Cells(iSBoMRow, iMTOmCurrency).Value
                    wsNewSheet.Cells(iNewSheetRow, 27).Value = wsSBoM.Cells(iSBoMRow, iMTOmBuildHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 28).Value = wsSBoM.Cells(iSBoMRow, iMTOmSysBuildHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 29).Value = wsSBoM.Cells(iSBoMRow, iMTOmTestHrs).Value
                    wsNewSheet.Cells(iNewSheetRow, 30).Value = wsSBoM.Cells(iSBoMRow, iMTOmEngHrs).Value
                End If
            End If
        Next iSBoMRow

        wsNewSheet.Range("G1:AD1").Formula = "=SUM(G2:G152)"
        wsNewSheet.Range("G1:AD1").NumberFormat = "0.00"
        buildRate = (wsNewSheet.Cells(1, 13).Value * 123.35)
        MTOBuildRate = (wsNewSheet.Cells(1, 27).Value * 123.35)
        sysBuildRate = (wsNewSheet.Cells(1, 14).Value * 123.35)
        MTOSysBuildRate = (wsNewSheet.Cells(1, 28).Value * 123.35)
        testRate = (wsNewSheet.Cells(1, 15).Value * 126.22)
        MTOTestRate = (wsNewSheet.Cells(1, 29).Value * 126.22)
        engRate = (wsNewSheet.Cells(1, 16).Value * 97.14)
        MTOEngRate = (wsNewSheet.Cells(1, 30).Value * 97.14)

        laborBuild = (wsNewSheet.Cells(1, 10).Value + buildRate)
        laborSysBuild = (wsNewSheet.Cells(1, 10).Value + sysBuildRate)
        laborTestBuild = (wsNewSheet.Cells(1, 10).Value + testRate)
        laborEngBuild = (wsNewSheet.Cells(1, 10).Value + engRate)
        laborMTOBuild = (wsNewSheet.Cells(1, 10).Value + MTOBuildRate)
        laborMTOSysBuild = (wsNewSheet.Cells(1, 10).Value + MTOSysBuildRate)
        laborMTOTestBuild = (wsNewSheet.Cells(1, 10).Value + MTOTestRate)
        laborMTOEngBuild = (wsNewSheet.Cells(1, 10).Value + MTOEngRate)

        laborTotal = (laborBuild + laborSysBuild + laborTestBuild + laborEngBuild)
        laborMTOTotal = (laborMTOBuild + laborMTOSysBuild + laborMTOTestBuild + laborMTOEngBuild)

    End If
Next iRow
End Function

Answer 1

However, when I try to store these values in the cells I want it will only return the most recent sheets values.

Because you only have one (i.e., the "most recent") value in any of your variables. So a loop like this takes that one most recent value and puts it in 10 different cells:

For i = 1 To 10
    wsTotals.Cells(1, i).Value = laborTotal
    wsTotals.Cells(2, i).Value = laborMTOTotal
Next i

This code is doing exactly what you have told it to do. This may differ from what you want it to do. So, what do you actually want it to do? This is a 1 to 10 loop, so the relationship between the new sheets (152 of them) you create at run-time and this loop is not immediately apparent.

So, when i = 1, laborTotal will be computed from the data on wsNewSheet.

When i = 2, how should this value be computed???

UPDATE FROM COMMENTS

I don't understand why you have For i = 1 to 10... which would be a nested loop within your iRow loop. As you have explained it, I think that is not necessary.

I think this should put the value of laborTotal in row 1 of the Totals sheet, and the value of laborMTOTotal in row 2 of Totals sheet.

    ...
    laborMTOTestBuild = (wsNewSheet.Cells(1, 10).Value + MTOTestRate)
    laborMTOEngBuild = (wsNewSheet.Cells(1, 10).Value + MTOEngRate)

    laborTotal = (laborBuild + laborSysBuild + laborTestBuild + laborEngBuild)
    laborMTOTotal = (laborMTOBuild + laborMTOSysBuild + laborMTOTestBuild + laborMTOEngBuild)

    '## ADD THESE LINES, or modify as needed:
    wsTotals.Cells(1, iRow).Value = laborTotal
    wsTotals.Cells(2, iRow).Value = laborMTOTotal

End If