Trying to copy and paste using different workbooks, but the correct value is not being copied

Question

I am trying to copy and paste in two different workbooks, but the correct value is not being copied. I believe it is because I am using the "with" statement within another "with" statement, but I do not know how to avoid this since I am using a for loop. Here is my code so far:

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

I hope the comments give you a good understanding as to what I am trying to do. Also, once I copy and paste all the values for that row, I would like the For Loop to continue not for that row, but for the next row. Any suggestions as to how I can make this happen? Thanks!

Remove the `with`-Statements and fully qualify the ranges, eg `AppFlowWkst.targetRng` — FunThomas, Feb 27 '17 at 17:15
Additionally, I think you have to `select` the worksheet before you can Paste into - one of the very rare situations where a `select` is really needed — FunThomas, Feb 27 '17 at 17:19
@FunThomas - The code is only pasting values - it should just be a direct assignment instead. — Comintern, Feb 27 '17 at 17:42

score 1 · Answer 1 · answered Feb 27 '17 at 17:18

I don't think that the dual with statements are your problem, but you can remove the second with:

Set targetRng = AppFlowWkst.Range("A" & Rows.Count).End(xlUp).Offset(1)
targetRng.PasteSpecial xlPasteValues

and see if it resolves your issue. It will at least let you remove that concern you have.

In fact you don't need either with statement and can do something like:

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

score 1 · Accepted Answer · answered Feb 27 '17 at 17:49

You're missing the . in front of Range on this line, so it's copying from the ActiveSheet:

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

It should be .Range("D" & (aCell.Row)).Copy.

That said, you don't even need to use .Copy and .Paste because the only thing that you're pasting are values. Just assign them directly instead:

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

Trying to copy and paste using different workbooks, but the correct value is not being copied

2 Answers2