VBA Paste from autofilter with additional data

[joeino]

Hello,

I have simple code (see below) that requires some tweaking. So basically, what it does is, it filters the data from another worksheet and paste it to a different worksheet. What I want to do is when I run the macro, it will paste the data onto the destination sheet on columns A to Q and the word "test" on the next column (Col R). So if I have data from A2 to Q15. I will also have the word "test" from R2 to R15. Thank you in advance.



With wsA
.AutoFilterMode = False
.Range("AR:AR").AutoFilter
.Range("AR:AR").AutoFilter Field:=1, Criteria1:="No"
On Error Resume Next
wsA.Range("A2:Q" & lr).SpecialCells(xlCellTypeVisible).Copy wsB.Range("A" & Rows.Count).End(3)(2)
On Error GoTo 0
.AutoFilterMode = False
End With

 

[Fluff]

How about

With wsA
.AutoFilterMode = False
.Range("AR:AR").AutoFilter
.Range("AR:AR").AutoFilter Field:=1, Criteria1:="No"
On Error Resume Next
   With WsB.Range("A" & Rows.Count).End(xlUp)
      wsA.Range("A2:Q" & lr).SpecialCells(xlCellTypeVisible).Copy .Offset(1)
      WsB.Range(.Offset(1, 17), WsB.Range("A" & Rows.Count).End(xlUp).Offset(, 17)).Value = "Test"
   End With
On Error GoTo 0
.AutoFilterMode = False
End With

 

[joeino]

How about

With wsA
.AutoFilterMode = False
.Range("AR:AR").AutoFilter
.Range("AR:AR").AutoFilter Field:=1, Criteria1:="No"
On Error Resume Next
   With WsB.Range("A" & Rows.Count).End(xlUp)
      wsA.Range("A2:Q" & lr).SpecialCells(xlCellTypeVisible).Copy .Offset(1)
      WsB.Range(.Offset(1, 17), WsB.Range("A" & Rows.Count).End(xlUp).Offset(, 17)).Value = "Test"
   End With
On Error GoTo 0
.AutoFilterMode = False
End With

Thank you Fluff! This is awesome!

 

[Fluff]

You're welcome & thanks for the feedback.