Macro to filter and copy

[Human2014]

Hello,

I am trying to find a way to use a macro that will filter column O on the different name I have.
Let's say that it will filter on "David" then it will copy from column A to P and paste in a new sheet.

I have the following code:

Please Login or Register  to view this content.

[stnkynts]

Try this:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

What you have looks good too me? Note the changes:

Please Login or Register  to view this content.

[Human2014]

How can I make it create a new sheet everytime I filter on a name ?

Thank you for this awesome code

What you have looks good too me? Note the changes:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe something like this.

Please Login or Register  to view this content.

[stnkynts]

Please Login or Register  to view this content.

[Human2014]

It shows an error message for this line

Please Login or Register  to view this content.

Sub CopyRange()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim response As String, ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
response = InputBox("please enter the name to filter.")
Sheets.Add.Name = "New Sheet " & response
ws.Activate
Sheets("Data").Range("O1:O" & LastRow).AutoFilter Field:=1, Criteria1:=response
Sheets("Data").Range("A1:P" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("New Sheet").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("Data").FilterMode Then Sheets("Data").ShowAllData
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
[/QUOTE]