-
Autofilter Help
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
ULLER
-
Uller,
Having a problem opening the file you attached.
Can you try attaching another copy of your file.
Have a great day,
Stan
-
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules