filtered data
 

I have a sheet with auto-filter turned on. I then have a macro that copies all the data from this sheet to another sheet and applies a specific filter. This is for the user to get a printed copy of only specific data. Here's the code

NumRows = Sheets("Details").Range("A8").CurrentRegion.Rows.C ount +
With Sheets("Newsprint"
.Visible = Tru
.Selec
.Rows("8:3000").ClearContent
End Wit
Sheets("Details").Range("A8:AM" & NumRows).Cop
Sheets("Newsprint").Range("A8").PasteSpecia
Selection.AutoFilter Field:=14, Criteria1:="N

The problem is once the autofilter of filed 14 is applied, this also hides the sub-total row. This doesn't do this on my original data sheet.

Any Ideas?

filtered data
 

If the last row of the table contains a SUBTOTAL formula, it won't be
filtered out. When you copy and paste the filtered data, it pastes
values only, so the last row is filtered as a regular row.

Erin wrote:
I have a sheet with auto-filter turned on. I then have a macro that copies all the data from this sheet to another sheet and applies a specific filter. This is for the user to get a printed copy of only specific data. Here's the code:

NumRows = Sheets("Details").Range("A8").CurrentRegion.Rows.C ount + 7
With Sheets("Newsprint")
.Visible = True
.Select
.Rows("8:3000").ClearContents
End With
Sheets("Details").Range("A8:AM" & NumRows).Copy
Sheets("Newsprint").Range("A8").PasteSpecial
Selection.AutoFilter Field:=14, Criteria1:="N"

The problem is once the autofilter of filed 14 is applied, this also hides the sub-total row. This doesn't do this on my original data sheet.

Any Ideas?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html