Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike

"JP" wrote:

This updated code will add the header and footer to the new worksheet.
It assumes that the footer text is in the worksheet in a range
A100:G103. If you put this text somewhere else, simply adjust the
range in the macro.


Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Excel.Range
Dim rng2 As Excel.Range
Dim NewSht As Worksheet

Set rng = ActiveSheet.UsedRange.Rows
' adjust the line below to fit your worksheet
Set rng2 = Range("A100:G103")

With rng
.AutoFilter Field:=6, Criteria1:="13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(Rows.count, _
Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set NewSht = Sheets.Add
rng.Copy NewSht.Range("A1")

rng2.Copy NewSht.Range("A65536").End(xlUp).Offset(2, 0)

End Sub

On Jan 10, 12:36 pm, Mike wrote:
WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP