Copying Filtered Data

[Shirley Munro]

I am running a Macro which filters data into 4 separate workbooks.
then need to copy the filtered data into the one workbook but when I d
this it is copying the full 65,000 rows. Is there something I ca
record in a Macro that will only copy down to the last cell with
value in it. The number of rows is going to change each time the macr
runs so I can't select the filtered data by itself.

thank

 

[Dave Peterson]

Does the problem exist in the Filtered range or does it exist in your code that
does the copying?

If you're filtering the whole worksheet, then this won't work. But if you're
copying rows (from 2 to the bottom of the worksheet--not the bottom of the
filtered range), it should work ok.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet

Dim rng As Range
Dim rngF As Range

Dim destCell As Range

Set fWks = Worksheets("sheet1")
Set tWks = Worksheets("sheet2")

With tWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With fWks
Set rng = .AutoFilter.Range
With rng
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "No detail rows shown!"
Else
Set rngF = .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
rngF.Copy _
Destination:=destCell
End If
End With
End With

End Sub

This actually copies the details--it doesn't include the autofilter header row.