try this
select any rep name in col a and run the macro
Sub filter()
Application.ScreenUpdating = False
If ActiveCell.Column = 1 Then
Z = Sheets("Input Data").Range("a1").End(xlDown).Row
On Error Resume Next
Sheets("Input Data").AutoFilterMode = False
Sheets("Input Data").Range("$A$1:$I" & Z).AutoFilter Field:=1,
Criteria1:=ActiveCell.Value
Sheets("Input Data").Range("$A$1:$I" &
Z).SpecialCells(xlCellTypeVisible).Copy
Sheets.Add After:=Sheets(Sheets.Count)
Cells(1, 1).Select
ActiveSheet.Paste
On Error Resume Next
ActiveSheet.Name = Cells(2, 1).Value
On Error Resume Next
Sheets("Input Data").AutoFilterMode = False
End If
Application.ScreenUpdating = True
End Sub
On Sat, Sep 10, 2011 at 5:10 PM, Steve Weaver <stevebwea...@comcast.net>wrote:
> Hi,
>
> Every month I download a single commission file which contains between 3 -
> 4 representatives. Currently I copy and paste the data for each rep into a
> unique work sheet so that the appropriate reports can be produced.
>
> I tried using advanced filtering but could not get the desired results. To
> write a MACRO to do this is beyond my technical ability.
>
> I'd like to find a way using either a macro OR advance filtering to do this
> work for me.
>
> Please send suggestions.
>
> Thank you!
>
> Steve
>
> --
> ------------------------------**------------------------------**
> ----------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/**
> exceldailytip <http://twitter.com/exceldailytip>
> 2. Join our LinkedIN group @
> http://www.linkedin.com/**groups?gid=1871310<http://www.linkedin.com/groups?gid=1871310>
> 3. Excel tutorials at
> http://www.excel-macros.**blogspot.com<http://www.excel-macros.blogspot.com>
> 4. Learn VBA Macros at
> http://www.quickvba.blogspot.**com<http://www.quickvba.blogspot.com>
> 5. Excel Tips and Tricks at
> http://exceldailytip.blogspot.**com<http://exceldailytip.blogspot.com>
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><>**<><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/**discussexcel<http://www.facebook.com/discussexcel>
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
http://akoul.posterous.com/
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
