Autofilter Help

[Uller]

Hey guys,

I am trying to write a macro that will autofilter the different user's in this workbook by using the header "username" and copy/paste that selection into a blank worksheet in the same workbook. My problem is that the users I am autofiltering will vary statement to statement, and right now I am essentially hardcoding the current user names into my code as you can see below. My goal is to have every user listed in a separate worksheet without hardcoding, which I know will cause errors in the future. I am confused on how to procede from here...

Sub Adshare2()

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="313citymoe"
Selection.CurrentRegion.Select
Selection.Copy
Sheets(3).Select
ActiveSheet.Paste
Sheets(3).Name = Range("D2").Value
Sheets(2).Activate
AutoFilterMode = False

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="AnnayaThePoet"
Selection.CurrentRegion.Select
Selection.Copy
Sheets(4).Select
ActiveSheet.Paste
Sheets(4).Name = Sheets(4).Range("D2").Value
Sheets(2).Activate
AutoFilterMode = False

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="bdubbtv"
Selection.CurrentRegion.Select
Selection.Copy
Sheets(5).Select
ActiveSheet.Paste
Sheets(5).Name = Sheets(5).Range("D2").Value
Sheets(2).Activate
AutoFilterMode = False

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="bloktv"
Selection.CurrentRegion.Select
Selection.Copy
Sheets(6).Select
ActiveSheet.Paste
Sheets(6).Name = Sheets(6).Range("D2").Value
Sheets(2).Activate
AutoFilterMode = False

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="bobbywashington"
Selection.CurrentRegion.Select
Selection.Copy
Sheets(7).Select
ActiveSheet.Paste
Sheets(7).Name = Sheets(7).Range("D2").Value
Sheets(2).Activate
AutoFilterMode = False

End Sub

I have attached a sample statement. Any help would be greatly appreciated!

Regards,

Uller

[stanleydgrom]

Uller,

Having a problem opening the file you attached.

Can you try attaching another copy of your file.


Have a great day,
Stan

[mdmackillop]

Run this in a book containing AllUsers sheet only
[VBA]
Sub Macro1()
Dim Rng As Range
Dim ws As Worksheet, Tgt As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("All Users")
Sheets.Add
ActiveSheet.Name = "Unique"
ws.Columns("D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Unique").Range("A1"), Unique:=True
With Sheets("Unique")
Set Rng = Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
For Each cel In Rng
Set Tgt = Sheets.Add(after:=Sheets(Sheets.Count))
Tgt.Name = cel
ws.Columns("D").AutoFilter Field:=1, Criteria1:=cel
ws.Cells.Copy Tgt.Range("A1")
Next
ws.Columns("D").AutoFilter
Application.ScreenUpdating = True
End Sub
[/VBA]