When copying filtered rows to another sheet I get an error

[timpepu]

Hi ! When I try to copy filtered row s to another sheet I get an error ,Run-time-error'1004' ,copy method of range class failed

Here is the code and I want also heeaders

Sub Macro3()
Dim Wb As Workbook
Dim ws As Worksheet
Set Wb = ThisWorkbook
Set ws = Wb.Sheets("HCBODM")

Range("A1:R1").Select
Selection.AutoFilter
' done

With ws
.AutoFilterMode = False
With .Range("A1:r40000")
.AutoFilter Field:=8, Criteria1:="<>CC*" _
, Operator:=xlOr, Criteria2:="CQN*"
.SpecialCells(xlCellTypeVisible).copy Destination:=Sheets("Taul2").Range("A1")
End With
End With
End Sub

 

[BarryL]

can you try the below? i simulated your data and the below worked for me.

Sub Macro3()
Dim Wb As Workbook
Dim ws As Worksheet
Set Wb = ThisWorkbook
Set ws = Wb.Sheets("HCBODM")
With ws
.AutoFilterMode = False
With .Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row)
.AutoFilter Field:=8, Criteria1:="<>CC*" _
, Operator:=xlOr, Criteria2:="CQN*"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Taul2").Range("A1")
End With
End With
End Sub

 

[timpepu]

can you try the below? i simulated your data and the below worked for me.

Sub Macro3()
Dim Wb As Workbook
Dim ws As Worksheet
Set Wb = ThisWorkbook
Set ws = Wb.Sheets("HCBODM")
With ws
.AutoFilterMode = False
With .Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row)
.AutoFilter Field:=8, Criteria1:="<>CC*" _
, Operator:=xlOr, Criteria2:="CQN*"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Taul2").Range("A1")
End With
End With
End Sub

It works Thanks

 

[BarryL]

No problem!

 

[timpepu]

It works Thanks

I get it work but now I get ann error ,stop here .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Taul2").Range("A1")