At ActiveSheet.Paste, try
ActiveSheet.PasteSpecial xlPasteValuesAndNumberFormats
That should give you only the data shown by the filter.

Ed

"Elinor Hartman" wrote in message
...
Hi Trevor,
I used your suggested code for Mr. B with minor changes. The difference
is I need to copy teh filtered data into a new worksheet within the
existing workbook and print it. It doesn't seem to paste the filtered
data into the new sheet. Do you have suggestions?

Option Explicit

Sub PAY3()
Dim masterWB As Workbook
Dim newWB As Workbook
Dim filterRange As Range
Dim cell As Range
Dim ws As Worksheet
Dim newSheetName As String
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set masterWB = ThisWorkbook
With masterWB
With ActiveSheet
' create a temporary list of unique SALESPERSONS
.Range("B:B").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
' loop through each of the unique SALESPERSONS
' and filter on that value
Set filterRange = .Range("iv2:iv" &
Range("iv2").End(xlDown).Row)
For Each cell In filterRange
With .Range("A1")
' filter on column B (field:=2)
.AutoFilter Field:=2, Criteria1:=cell
newSheetName = cell.Value
' copy the current range, visible cells
.CurrentRegion.Copy
'Sheets(cell.Value).Add
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
Sheets(newSheetName).Select
Range("A4").Select
' paste the data
ActiveSheet.Paste

.AutoFilter
Application.CutCopyMode = False
End With
Next 'cell
' clear the temporary list of unique SALESPERSONS
filterRange.Offset(-1, 0).Resize( _
filterRange.Rows.Count + 1, filterRange.Columns.Count).Clear
End With
End With
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Thank You,
Elinor



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!