VBA Filter
 

Right, im no novice, but im way short of being able to sort this little issue out on my jack jones.

Ive got a excel ss that has 80 lines of information and each line is reportable to one of 20 different people, I need VBA to to pull together all the lines that have the reportable name at the end of the line, eg EntriesErrorsEntriesHash TotalManager1000Jo Blogs
I need VBA to match all the names together and then dump into a new workbook, then save the file as the name in cell N2. the below is what ive come up with so far, but im lost on how i can get the VBA to select the new name from the filter list.

Sub copydata()
ActiveSheet.Range("$A$1:$N$419").AutoFilter Field:=14, Criteria1:= _
"Jo Blogs"
Cells.Select
Range("B1").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("N2").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Jo Blogs"
ActiveWorkbook.SaveAs Filename:= _
"hidden"", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cells.Select
Selection.ClearContents
Windows("Drubs.xls").Activate


End Sub

VBA Filter
 

On Wednesday, October 24, 2012 9:18:29 AM UTC-5, Fleetzy wrote:
Right, im no novice, but im way short of being able to sort this little

issue out on my jack jones.



Ive got a excel ss that has 80 lines of information and each line is

reportable to one of 20 different people, I need VBA to to pull together

all the lines that have the reportable name at the end of the line, eg

EntriesErrorsEntriesHash TotalManager1000Jo Blogs

I need VBA to match all the names together and then dump into a new

workbook, then save the file as the name in cell N2. the below is what

ive come up with so far, but im lost on how i can get the VBA to select

the new name from the filter list.



Sub copydata()

ActiveSheet.Range("$A$1:$N$419").AutoFilter Field:=14, Criteria1:= _



"Jo Blogs"

Cells.Select

Range("B1").Activate

Selection.Copy

Workbooks.Add

ActiveSheet.Paste

Range("N2").Select

Application.CutCopyMode = False

Selection.Copy

Application.CutCopyMode = False

Selection.Copy

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "Jo Blogs"

ActiveWorkbook.SaveAs Filename:= _

"hidden"", _

ReadOnlyRecommended:=False, CreateBackup:=False

Cells.Select

Selection.ClearContents

Windows("Drubs.xls").Activate





End Sub









--

Fleetzy

Can't tell what you need without seeing. Send this msg, your file and complete expalanation to dguillett @gmail.com

VBA Filter
 

I had this same issue that I needed help with several years ago. Ron De Bruin helpe me immensely. He has a webpage article detailing how to do exactly this (using entries in a specific column, create new workbooks or worksheets based on unique values).

http://www.rondebruin.nl/copy5.htm