CODEWITHSUNDEEP
excelconditional-statementscopy-pastevba
Dad Walsh
Dad Walsh

Reputation: 15

VBA to Copy from Named Range based on Condition to Paste Vlaues in another worksheet

I have a Named Range "Quantities" (Worksheet Sheet1, cells I21:L28) that have formula that reports a the quantity in column "L". I would like to search Column L for values >0, and then Paste those Values (along with Data from Column K) into another worksheet (Sheet10). The following Code is close, but it paste the formula not the values. Please assist.

Sub CopyOnCondition()
     Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
     Set sh1 = Sheet1 'Edit sheet name
     Set sh2 = Sheet10 'Edit sheet name
     With sh1
         For Each c In .Range("L18:L24")
             If c.Value > 0 Then
                 c.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2, 1)
             End If
         Next
     End With
End Sub

Upvotes: 0

Views: 513

Answers (2)

SJR
SJR

Reputation: 23081

Try this

Sub CopyOnCondition()
     Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
     Set sh1 = Sheet1 'Edit sheet name
     Set sh2 = Sheet10 'Edit sheet name
     With sh1
         For Each c In .Range("L18:L24")
             If c.Value > 0 Then
                 sh2.Cells(Rows.Count, 1).End(xlUp)(2, 1).resize(,2).value=c.offset(,-1).resize(,2).value
             End If
         Next
     End With
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

The code below will copy the values only from Columns K:L when the value in column L > 0.

Sub CopyOnCondition()

     Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
     Set sh1 = Sheet1 'Edit sheet name
     Set sh2 = Sheet10 'Edit sheet name

     With sh1
         For Each c In .Range("L18:L24")
             If c.Value > 0 Then
                 c.Offset(, -1).Resize(1, 2).Copy '<-- copy column K with L
                 ' paste values to the first empty row in Column A of sh2
                 sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
             End If
         Next c
     End With

End Sub

Upvotes: 0

Related Questions