Copying Data into another worksheet using macro

[Andy W]

Hi all,

I am using the code generously supplied by Tom O below,
but have found that if a filter returns no result, i.e. if
the data doesn't exist in the list, it then proceeds to
paste the entire table into the destination worksheet.

What I need to happen is if no result is returns, then
nothing gets pasted (or a blank row gets pasted).

Any ideas?

Thanks in advance

Andy ;-)

Dim rng as Range
Sheets("Raw Data").Activate
If ActiveSheet.AutofilterMode then
Activesheet.AutofilterMode = False
With Range("A2")
.AutoFilter Field:=2, Criteria1:="PB to Cust*"
End with
With Activesheet.Autofilter.Range
set rng = .offset(1,0).Resize(.rows.count-1)
End With
rng.copy Destination:=Sheets("CustbyRDC").Range("A7").

if your table actually starts in A1 with a header row,
change A2 above to
A1.

 

[Tom Ogilvy]

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