Using Filters, Copy and Paste in a macro
 

Hi All,

I am trying to Copy the results of a filter into a
separate worksheet, but have found that it is copying the
occupied cells, plus all of the blank ones below. I don't
know how to only select the occupied cells, so I have
selected rows 2 - 1001.

The formula I am using is below:

Sub SecondStep()
'
' SecondStep Macro
' Macro recorded 18/02/2004 by Andy
'
Sheets("Raw Data").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="PB to Cust*"
Range("A2:F1001").Select
Selection.Copy
Sheets("CustbyRDC").Select
Range("A7").Select
ActiveSheet.Paste
Sheets("Raw Data").Select
'
End Sub



Please help!! I am at the end of my tether!!

Thanks in advance

Andy

Using Filters, Copy and Paste in a macro
 

Andy,

Replace

Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="PB to Cust*"
Range("A2:F1001").Select
Selection.Copy

with

With Range("A2").CurrentRegion
.AutoFilter Field:=2, Criteria1:="13"
.SpecialCells(xlVisible).Copy
.Copy
End With

HTH,
Bernie
MS Excel MVP

"Andy W" wrote in message
...
Hi All,

I am trying to Copy the results of a filter into a
separate worksheet, but have found that it is copying the
occupied cells, plus all of the blank ones below. I don't
know how to only select the occupied cells, so I have
selected rows 2 - 1001.

The formula I am using is below:

Sub SecondStep()
'
' SecondStep Macro
' Macro recorded 18/02/2004 by Andy
'
Sheets("Raw Data").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="PB to Cust*"
Range("A2:F1001").Select
Selection.Copy
Sheets("CustbyRDC").Select
Range("A7").Select
ActiveSheet.Paste
Sheets("Raw Data").Select
'
End Sub



Please help!! I am at the end of my tether!!

Thanks in advance

Andy