Paste loop is not pasting values

Question

Sub buildPlan()
'
' buildPlan Macro
'
    
   Dim wb As Workbook
    Dim rwDest As Range, rw As Range, valQ, valT
    Dim nwb As Workbook, wsAPP As Worksheet, wsDNDR As Worksheet
    
    Set wb = Application.ActiveWorkbook           'ThisWorkbook?
    Set wsAPP = wb.Worksheets("Arils Pack Plan ") 'trailing space?
    
    'Opening Recent ATS report
     With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
    Application.Workbooks.Open .SelectedItems(1)
    Set nwb = Application.ActiveWorkbook

    End With
    
    
    Set wsDNDR = nwb.Worksheets("DAILY NEED (DR)")
    
    Set rwDest = wsAPP.Rows(7) 'start row for results
    
    '4oz Day 1 -----------------------------------------------------------------------
    
    For Each rw In wsDNDR.Range("Q5:Q14").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("B7").Value = rw.Columns("B7").Value 'SKU
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw
    
    
    For Each rw In wsDNDR.Range("Q5:Q14").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("E7").Value = rw.Columns("E7").Value 'VALUE
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw

    For Each rw In wsDNDR.Range("Q5:Q14").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("F7").Value = valQ 'Value
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw

    '4oz Day 2---------------------------------------------------------------------------------------
    
    For Each rw In wsDNDR.Range("T5:T14").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("B7").Value = rw.Columns("B7").Value 'SKU
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw
    
    
   For Each rw In wsDNDR.Range("T5:T14").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("E7").Value = rw.Columns("E7").Value 'Pallet Type
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw


    For Each rw In wsDNDR.Range("T5:T14").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("F7").Value = valT 'Value
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw


    '8oz Day 1---------------------------------------------------------------------------------------------

    For Each rw In wsDNDR.Range("Q15:Q26").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("B7").Value = rw.Columns("B7").Value 'SKU
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw
    
    
   For Each rw In wsDNDR.Range("Q15:Q26").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("E7").Value = rw.Columns("E7").Value 'Pallet Type
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw


    For Each rw In wsDNDR.Range("Q15:Q26").Rows
        valQ = rw.Columns("Q").Value
        If valQ < 0 Then
            rwDest.Columns("F7").Value = valQ 'Value
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw

    '8oz Day 2---------------------------------------------------------------------------------------------
        
    For Each rw In wsDNDR.Range("T15:T26").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("B7").Value = rw.Columns("B7").Value 'SKU
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw
    
    
   For Each rw In wsDNDR.Range("T15:T26").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("E7").Value = rw.Columns("E7").Value 'Pallet Type
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw


    For Each rw In wsDNDR.Range("T15:T26").Rows
        valT = rw.Columns("T").Value
        If valT < 0 Then
            rwDest.Columns("F7").Value = valT 'Value
            Set rwDest = rwDest.Offset(1) 'Next row
        End If
    Next rw

End Sub

I am trying to paste the values from the Tracker to the Pack Plan if they are negative. I need to past the values from section 3 to column F in the pack plan. With its corresponding cells in section 1 and 2 from the tracker, then repeat that process if the values in section 4 are negative. Section 1 needs to be pasted in column B of the pack plan and section 2 needs to be pasted in column E of the pack plan. I need to finish the top sections (1-4) before moving onto the bottom sections (5-8). The process will be the same. I need to copy and paste the values with their corresponding cells in 5 and 6. Once the top section is pasted in the pack plan I need the bottom section to paste below the the top section on the pack plan.

.Columns("B7") and .Columns("E7") look off. Columns does not use row numbers. So .Columns("B") or .Columns("E") or .Columns(2) or .Columns(5) is valid. — BigBen
– BigBen, Commented Jan 30, 2023 at 16:26
Always helps to link to your previous post. If you get an answer but it doesn't do what you need then you should ideally follow up in that same post, rather than post a new question. stackoverflow.com/a/75261506/478884 — Tim Williams
– Tim Williams, Commented Jan 30, 2023 at 16:28
wsDNDR.Range("Q5:Q14").Rows is already limiting itself to Column Q, which means rw.Columns("Q") is moving over as if starting from Column A, but since it was already in column Q, it is adding those two offsets together, retrieving Column 33 (AG) — Toddleson
– Toddleson, Commented Jan 30, 2023 at 16:29
Okay. How would I go about setting multiple ranges and seeing if the value with in that range is negative copy a specific value and paste it in another cell in a different workbook. I believe the code above uses too many functions versus just have more base code. — Jonathan Anguiano
– Jonathan Anguiano, Commented Jan 30, 2023 at 16:33

Tim Williams · Accepted Answer · 2023-01-30 17:18:13Z

1

Here's a slight modification of my answer in your previous post:

Sub buildPlan()
    Dim wb As Workbook
    Dim rwDest As Range, rw As Range, sRng, rngLoop As Range
    Dim nwb As Workbook, wsAPP As Worksheet, wsDNDR As Worksheet
    
    Set wb = Application.ActiveWorkbook           'ThisWorkbook?
    Set wsAPP = wb.Worksheets("Arils Pack Plan ") 'trailing space?
    
    'Opening Recent ATS report
     With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        If .Show = -1 Then Set nwb = Workbooks.Open(.SelectedItems(1))
    End With
    If nwb Is Nothing Then Exit Sub 'no file selected...
    Set wsDNDR = nwb.Worksheets("DAILY NEED (DR)")
    
    Set rwDest = wsAPP.Rows(7) 'start row for results
    
    For Each sRng In Array("A5:T14", "A15:T25") 'row ranges to loop over
        Set rngLoop = wsDNDR.Range(sRng)        'rows to loop over
        For Each rw In rngLoop.Rows
            CheckRow rw, rwDest, "Q" 'check col Q
        Next rw
        For Each rw In rngLoop.Rows
            CheckRow rw, rwDest, "T" 'check Col T
        Next rw
    Next sRng
End Sub

'If the value in column `onHandColLett` of `rwSrc` is negative
'   then transfer some values to `rwDest`
Sub CheckRow(rwSrc As Range, rwDest As Range, onHandColLett)
    Dim v
    v = rwSrc.Columns(onHandColLett).Value
    If v < 0 Then
        rwDest.Columns("B").Value = rwSrc.Columns("B").Value 'SKU
        rwDest.Columns("E").Value = rwSrc.Columns("E").Value 'pallet
        rwDest.Columns("F").Value = v                        'on hand value
        Set rwDest = rwDest.Offset(1)                        'Next row
    End If
End Sub

edited Jan 30, 2023 at 17:18

answered Jan 30, 2023 at 16:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jonathan Anguiano Over a year ago

I don't know if I am reading this code correctly, but I see that it will go through column q while copying column B completely. However, I need it to stop at row 14 then move to column t and repeat the process it was doing for column q. And repeat that process for rows 15:26.

Jonathan Anguiano Over a year ago

The reason being is I need it to paste the columns in order in the pack plan. I need rows 5:14 to be first with information from all the columns Q(Q being first) and T being the criteria, while also pasting the values in those columns. Then I need it to repeat that process for rows 15:26 using those same columns.

Tim Williams Over a year ago

OK - made some changes above - it will loop over A5:T14 then A15:T25, and check col Q and then col T for each of those ranges.

Jonathan Anguiano Over a year ago

This works perfect! Thank you! Also, is there a way to add a space when copying to the new workbook between the pasting of the top section and bottom section?

Tim Williams Over a year ago

You can call Set rwDest = rwDest.offset(1) to move the output row down

|

Collectives™ on Stack Overflow

Paste loop is not pasting values

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related