Search value, offset 3 columns, select and paste into new worksheet

[jgravesNew]

Hi all,
I've been trying for a while to get this to work! I am looking to find "Yes" in my column D range (D24:D75). If found, move to the corresponding cell in column A, copy that value and move it into row 3 in my Target sheet. Then find next "Yes", go to column A, copy, and paste to row 4 in my Target sheet...and so on.

Any help is greatly appreciated!

Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Rebalance")
Set Target = ActiveWorkbook.Worksheets("SellList")

j = 3 ' Start copying to row 3 in target sheet
For Each c In Source.Range("D24:D75")
If c = "Yes" Then
Cells.Activate
With ActiveCell
ActiveCell.Offset(0, -3).Copy Destination:=Target.Rows(j)
j = j + 1
End With
End If
Next c
End Sub

 

[Fluff]

Try

Target.Cells(j,1)

 

[jgravesNew]

Hi there, thank you for the help!
looks like it still hits an application-defined or object-defined error here:
ActiveCell.Offset(0, -3).Copy Destination:=Target.Cells(j, 1)

 

[Fluff]

How about

Sub CopyYes()
   Dim c As Range
   Dim j As Integer
   Dim SourceWs As Worksheet, TargetWs As Worksheet
   
   Set SourceWs = ActiveWorkbook.Worksheets("Rebalance")
   Set TargetWs = ActiveWorkbook.Worksheets("SellList")
   
   j = 3 ' Start copying to row 3 in target sheet
   For Each c In Source.Range("D24:D75")
      If c = "Yes" Then
         c.Offset(0, -3).Copy Destination:=Target.Cells(j, 1)
         j = j + 1
      End If
   Next c
End Sub

 

[jgravesNew]

Unfortunately not, still tripping up here. Receiving an Object Required error on this line. When I step through, once it comes to a "Yes" in my column D range (D24:D75) then it errors.
c.Offset(0, -3).Copy Destination:=Target.Cells(j, 1)

 

[Fluff]

Oops missed a correction. It should be

         c.Offset(0, -3).Copy Destination:=TargetWs.Cells(j, 1)

I change the variable Target to TargetWs as it's best to avoid using VBA keywords as variables

 

[jgravesNew]

Perfect, thank you very much! Funny, I caught it in For Each c In Source.Range("D24:D75") and updated to SourceWs there.

Missed this one. Appreciate the help!

 

[Fluff]

You're welcome & thanks for the feedback