Copy to next blank row

[horizonflame]

Hi,

I am performing a filter and copy/paste to next available row in another sheet, can anyone spot my error in my last part of my code please?

Thanks

Sheet3.Select

Dim sInput As String

sInput = Sheet1.Range("C2").Value
Sheet3.Range("A:A").AutoFilter Field:=1, Criteria1:="=*" & sInput & "*"

Range("A:A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[DanteAmor]

In this part, you are copying the entire column. Not only are the cells visible in the filter, you are also copying the blank cells that are below the filter. Then it is possible that the number of cells does not fit in the destination column.

[COLOR=#ff0000]Range("A:A").Select[/COLOR]
[COLOR=#ff0000]Selection.SpecialCells(xlCellTypeVisible).Select[/COLOR]
[COLOR=#ff0000]Selection.Copy[/COLOR]

Try this

Sub test()
  Dim sInput As String
  sInput = Sheet1.Range("C2").Value
  sheet3.Range("A:A").AutoFilter Field:=1, Criteria1:="=*" & sInput & "*"
  sheet3.AutoFilter.Range.Copy
  Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[horizonflame]

Brilliant thanks @DanteAmor

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.