尝试使用不同的工作簿复制和粘贴，但没有复制正确的值[重复]

Question

Sub Approval_Flow()
Dim AppFlowWkb As Workbook, ConfigWkb As Workbook
Dim AppFlowWkst As Worksheet, ConfigWkst As Worksheet
Dim aCell As Range
Dim targetRng As Range



Set AppFlowWkb = Workbooks.Open("C:\Users\clara\Documents\Templates and Scripts\Flow Change.xlsx")
Set ConfigWkb = ThisWorkbook
Set AppFlowWkst = AppFlowWkb.Sheets("Editor")
Set ConfigWkst = ConfigWkb.Worksheets("Approval Flows")

With ConfigWkst
    'looking through each column value before moving on to next row
    For Each aCell In .Range("A1:K" & .UsedRange.Rows.Count)
        'if cell is highlighted, copy that row's column D value
        If Not aCell.Interior.Color = RGB(255, 255, 255) Then
            Range("D" & (aCell.row)).Copy
            'in appflow workbook, find empty row in column A and paste
            With AppFlowWkst
                Set targetRng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
                targetRng.PasteSpecial xlPasteValues
            End With

            'Range("C" & (aCell.row)).Copy
            'With AppFlowWkst
                'Set targetRng = .Range("B" & Rows.Count).End(xlUp).Offset(1)
                'targetRng.PasteSpecial xlPasteValues

'once the rows' values have been pasted, move on to next row


        End If
    Next aCell

End With


End Sub

Answer 1

在这一行的区域前面缺少.，所以它是从ActiveSheet复制的：

Range("D" & (aCell.Row)).Copy

它应该是.range（“d”&(acell.row)).copy。

也就是说，您甚至不需要使用.copy和.paste，因为您粘贴的只是值。直接分配它们就可以了：

With ConfigWkst
    For Each aCell In .Range("A1:K" & .UsedRange.Rows.Count)
        If Not aCell.Interior.Color = RGB(255, 255, 255) Then
            Set targetRng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
            'Use a direct assignment.
            targetRng.Value = .Range("D" & (aCell.Row)).Value
        End If
    Next aCell
End With