Trying to paste into a worksheet

[Andy W]

Dear All,

I am using the below to search, copy + paste data into a
worksheet. The code finds the results OK, but isn't
pasting anything, and just jumping straight into the
MsgBox. Any ideas what I've done wrong anyone??

Yours ever so hopefully ;-)

Andy W

------------

Sub BBBBB()
Dim rng As Range
Dim bCopy As Boolean
Sheets("Raw Data").Activate
If ActiveSheet.AutoFilterMode Then _
ActiveSheet.AutoFilterMode = False
With Range("A1")
.AutoFilter Field:=2, Criteria1:="=*Z*"
End With

With ActiveSheet.AutoFilter.Range
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1)
bCopy = False
If .SpecialCells(xlVisible).Columns(1) _
.Rows.Count > 1 Then _
bCopy = True
End With
If bCopy Then
rng.Copy Destination:=Sheets("CustbyRDC").Range("A7")
Else
MsgBox "No rows to copy"
End If
ActiveSheet.AutoFilterMode = False
End Sub
..

 

[Tom Ogilvy]

there shouldn't be a .rows.count. It should just be a .count. My mistake.
Try this one: (worked for me).

Sub BBBBB()
Dim rng As Range
Dim bCopy As Boolean
Sheets("Raw Data").Activate
If ActiveSheet.AutoFilterMode Then _
ActiveSheet.AutoFilterMode = False
With Range("A1")
.AutoFilter Field:=2, Criteria1:="=*Z*"
End With

With ActiveSheet.AutoFilter.Range
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1)
bCopy = False
If .Columns(1).SpecialCells(xlVisible) _
.Count > 1 Then _
bCopy = True
End With
If bCopy Then
rng.Copy Destination:=Sheets("CustbyRDC").Range("A7")
Else
MsgBox "No rows to copy"
End If
ActiveSheet.AutoFilterMode = False
End Sub