Filter by each availible variable and save to a new workbook

[DrSmyth]

I need to filter an Excel spreadsheet by each availible variable in a column, and after each filter i need to create a new spreadsheet with the filter variable being used in the title.

eg:

Column 3
a
b
c
c
a

This would filter the spreadsheet 3 times (one for each different row) and give me new workbooks called a, b and c...

I've been asked by my boss to do this and i don't have a clue, so any help at all would be very very appreciated....

 

[xlbo]

This should pretty much do what you want:

Sub test()
Dim AllVar() As Variant, addToArr As Boolean, ctr As Long, lRow as long
ctr = 0
ReDim AllVar(999) 'any value that is higher than the expected number of unique entries
lRow = Cells(65536, 1).End(xlUp).Row
For i = 2 To lRow
addToArr = True
For x = LBound(AllVar) To UBound(AllVar)
If AllVar(x) = Cells(i, 1).Text Then addToArr = False
Next x

Select Case addToArr
Case True
For x = LBound(AllVar) To UBound(AllVar)
If AllVar(x) = "" Then
AllVar(x) = Cells(i, 1).Text
ctr = ctr + 1
Exit For
Else
End If
Next x
Case False

End Select
Next i
ReDim Preserve AllVar(ctr - 1)
For x = LBound(AllVar) To UBound(AllVar)
Range("A1:Z" & lRow).AutoFilter field:=1, Criteria1:=AllVar(x)
Workbooks.Add
ThisWorkbook.ActiveSheet.Range("A1:Z" & lRow).Copy Destination:=ActiveWorkbook.Sheets(1).Range("A1&quot
ActiveWorkbook.SaveAs Filename:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & AllVar(x) & ".xls"
Next x
End Sub

Assumes data starts in A2(headers in row 1) and goes across to Z (change Z in code to suit)
Also assumes that the entries to be picked up are in col A

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DrSmyth]

Cheers, I'll give it a go and get back to you....

 

[DrSmyth]

I've run the code on the workbook and it seems to mostly be working, but i have a couple of questions.

Firstly, if i wanted to filter by column 3, where would i change the code to reflect this?

Secondly, athough it is creating a new document for each filter variable, the newdocuments are all the same, they are each using the first filter value.

Thanks again for your help here xblo, i'll be giving you a star as this is a very useful piece of code.

 

[xlbo]

To filter by column 3:

Sub test()
Dim AllVar() As Variant, addToArr As Boolean, ctr As Long, lRow as long
ctr = 0
ReDim AllVar(999) 'any value that is higher than the expected number of unique entries
lRow = Cells(65536, 3).End(xlUp).Row
For i = 2 To lRow
addToArr = True
For x = LBound(AllVar) To UBound(AllVar)
If AllVar(x) = Cells(i, 3).Text Then addToArr = False
Next x

Select Case addToArr
Case True
For x = LBound(AllVar) To UBound(AllVar)
If AllVar(x) = "" Then
AllVar(x) = Cells(i, 3).Text
ctr = ctr + 1
Exit For
Else
End If
Next x
Case False

End Select
Next i
ReDim Preserve AllVar(ctr - 1)
For x = LBound(AllVar) To UBound(AllVar)
Range("A1:Z" & lRow).AutoFilter field:=3, Criteria1:=AllVar(x)
Workbooks.Add
ThisWorkbook.ActiveSheet.Range("A1:Z" & lRow).Copy Destination:=ActiveWorkbook.Sheets(1).Range("A1&quot
ActiveWorkbook.SaveAs Filename:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & AllVar(x) & ".xls"
Next x
End Sub

As to the workbooks using the same name - that is puzzling as it is using
criteria1:=AllVar(x)
and
Filename:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & AllVar(x) & ".xls"

As you can see, the same AllVar(x) is being used for both so I'm a little confused - if the filter works, the naming should work - note that the same name will be used but the filter variable will be added to the end

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DrSmyth]

Sorry taken so long to get back, been working on some Business Objects stuff for a bit, so the excel projects have taken a bit of a backburner...

Was wondering, the part of this code that builds the array seems to take an age to run, is it possible to pivot the information in the column i want to use for my variables and then use the pivot table as the array...

I'm very much an excel novice at the moment, so your probably gonna give me a host of reasons why this won't work.... But I thought i'd give it a go..

 

[xlbo]

You could definitely do it with a pivot table but I have replied to the new thread with an easier solution, based on being able to SORT the data

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244