Excel VBA：复制单元格内容 [英] Excel VBA: Copying Cell Contents

问题描述

我有一个运行产品配置测试计划的excel程序。在此程序中，用户单击测试计划按钮，然后将对每个产品运行配置。当这种情况发生时，来自其他表单的信息被收集以找出成本和总额。我已经能够收集每列中的总成本，乘以费率，并将每个总计加在一起，以获得总劳动力成本，总材料成本，总劳动MTO成本和总材料MTO成本。我很想将这些总计存储在另一个表中，称为总计，所以我可以很容易地监视和操纵他们以后。

但是，当我尝试存储这些在我想要的单元格中的值将只返回最近的工作表值。 （重要的是注意，当程序通过点击按钮运行时，旧数据的旧表将被删除并替换为较新的数据，这意味着我不能在工作表中放置任何公式，它必须是VBA代码，不知道有多少张纸。）我尝试了许多不同的方法，其中两个我会发布。

  i = 1 To 10 
 wsTotals.Cells（1，i）.Value = laborTotal 
 wsTotals.Cells（2，i）.Value = laborMTOTotal 
 Next i 
  

这里我尝试将计算出的值存储在总计表格中。但是当它运行时，只复制2个最近的值。 （laborTotal在行1，A到J和laborMTOTotal在行2，A到J）。

我也尝试过：

  wsNewSheet.Range（AN1：AO1）。复制'我存储的总计远离视力，试图复制它们后来
 wsTotals 。选择
 wsTotals.Range（A1：J2）。选择
 ActiveSheet.Paste 
  

$ b b

这返回了类似的结果。任何帮助是非常感激，我可以提供更多的信息，如果必要。

编辑：我还应该补充说，当程序运行时，将为用户在测试计划表单上输入的每个智能号码创建一个新工作表。在我的代码中，我只是循环遍历每个表，以执行相应的任务。我的问题是，它不是抓住每一个总（或者它只是替换的总计在最后由最前面的）。每个新工作表都存储在wsNewSheet中。

好吧，让我再试一次。这是我的代码：

 函数Test_MLFB（sMLFB，sPattern）作为布尔
设置re = New RegExp 
 re.Pattern =^&替换（sPattern，...，...）& $
 re.IgnoreCase = False 
 Test_MLFB = re.Test（sMLFB）
结束函数
 
函数Test_Option_Code（sCode，sPattern）As Boolean 
设置re =新的RegExp 
 bInvert = False 
如果Left（sPattern，1）=！然后
 sPattern = Right（sPattern，Len（sPattern） -  1）
 bInvert = True 
 End If 
 re.Pattern =^。*& sPattern& 。* $
如果bInvert Then Test_Option_Code = Not（re.Test（sCode））Else Test_Option_Code = re.Test（sCode）
结束函数
 
功能Test_MLFB_OPTS ，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）&替换（sMLFBm，...，...）
 Test_MLFB_OPTS = re.Test（sConfiguration）
结束函数
 
函数f_Lookahead（sOpt_mask）As String 
如果sOpt_mask =那么
 f_Lookahead =
 ElseIf Left（sOpt_mask，1）=！然后'负前瞻断言
 f_Lookahead =（？！。*& Right（sOpt_mask，Len（sOpt_mask）-1）&）
否则肯定的前瞻断言
 f_Lookahead =（？=。*& sOpt_mask&）
结束如果
结束函数
 
函数Run_Test（）
 Remove_Old_Instance_BoMs 
 Run_Test_Case 
 
结束函数
 
函数Remove_Old_Instance_BoMs（）
 
 iSheetCount = ThisWorkbook.Sheets.Count 
对于每个aSheet在ThisWorkbook.Sheets 
如果不是（aSheet.Name =测试计划或aSheet.Name =SBoM或aSheet.Name =变形金刚SN或aSheet.Name =表或aSheet.Name =阶段_ 
 or aSheet.Name =bomcost.csv或aSheet.Name =bomtrafocost.csv或aSheet.Name =bomhourlyrate.csv或aSheet.Name =base_ 
或aSheet.Name =数字9或aSheet.Name =digit 14或aSheet.Name =digit 15或aSheet.Name =options或aSheet.Name =rates_ 
或aSheet.Name =opt short desc或aSheet.Name =Totals或aSheet.Name =bomcostINFO或aSheet.Name =bomcost或aSheet.Name =trans）然后
'MsgBox ThisWorkbook.Sheets（i）.Name 
 aSheet.Delete 
 End If 
 Next 
结束函数
 
函数Run_Test_Case（）
总计为工作表
 Dim bApplies as Boolean 
 Dim buildRate As long 
 Dim sysBuildRate As Long 
 Dim testRate As Long 
 Dim engRate As Long 
'以下是SBOM的列
 iType = 5 
 iPosNum = 6'f 
 iQtyNum = 7 
 iMatlNum = 8 
 iMatlDesc = 9 
 imlfbmask = 11 
 iOpt1Mask = 12 $ b b 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 '测试计划行
设置curTestCase = Worksheets（测试计划）。单元格（iRow，1）
设置curMLFBCell = Worksheets b设置curoptlistcell =工作表（测试计划）单元格（iRow，3）
设置wsSBoM =工作表（SBoM）
设置wsTotals = Worksheets（Totals）
如果不（IsEmpty（curMLFBCell））和Not（IsEmpty（curoptlistcell））然后
设置wsNewSheet = Worksheets.Add 
 wsNewSheet.Name = curTestCase.Value 
工作表iRow）.Copy 
 wsNewSheet.Rows（1：1）。选择
 wsNewSheet.Paste 
 
 iNewSheetRow = 2 
 For iSBoMRow = 4 To 1271 
 bApplies = False 
如果不是IsEmpty（wsSBoM.Cells（iSBoMRow，imlfbmask））然后
 bApplies = Test_MLFB_OPTS（curMLFBCell.Value& cueptlistcell.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）
如果
 
结束如果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 
如果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 
如果
结束如果
结束下一个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 ）.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 ）.Value * 97.14）
 
 labourBuild =（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）
 labourMTOTotal =（laborMTOBuild + laborMTOSysBuild + laborMTOTestBuild + laborMTOEngBuild）
 
结束如果
下一个iRow 
结束函数
  

因为您在任何变量中只有一个（即最近）值。因此，这样的循环需要一个最新值，并将其放在10个不同的单元格中：

  For i = 1 To 10 
 wsTotals.Cells（1，i）.Value = laborTotal 
 wsTotals.Cells（2，i）.Value = laborMTOTotal 
 Next i 
  

这段代码正是你告诉它做的。这可能与您想要的不同。那么，你实际上想要什么呢？ 这是一个 1到10 循环，因此您在运行时创建的新工作表（其中152个）和此循环之间的关系不会立即显现。

因此，当 i = 1， laborTotal wsNewSheet 上的数据。

当 i = 2时，应如何计算此值 / p>

UPDATE FROM COMMENTS

我不明白为什么你有对于i = 1到10 ... ，这将是您的 iRow 循环中的嵌套循环。

我认为这应该是的劳动值在 Totals 工作表的第1行中，以及第2行中的 laborMTOTotal 工作表。

  ... 
 laborMTOTestBuild =（wsNewSheet.Cells ）.Value + MTOTestRate）
 laborMTOEngBuild =（wsNewSheet.Cells（1，10）.Value + MTOEngRate）
 
 laborTotal =（laborBuild + laborSysBuild + laborTestBuild + laborEngBuild）
 laborMTOTotal =（laborMTOBuild + laborMTOSysBuild + laborMTOTestBuild + laborMTOEngBuild）
 
'##添加这些行，或根据需要修改：
 wsTotals.Cells（1，iRow）.Value = laborTotal 
 wsTotals.Cells（2，iRow）.Value = laborMTOTotal 
 
如果
结束 

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

解决方案

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

这篇关于Excel VBA：复制单元格内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！