File Name based on AutoFilter criteria 1

[Tiglet]

Hi,

I am creating a new file from a list of rows which have columns A to U populated. The list has an Auto filter. This is then copied to a new file and the user has the option to save the file. The file name is prepopulated based on a variable and the current date using the following.

Application.Dialogs(xlDialogSaveAs).Show (TabName & FormatDateTime(Date, vbLongDate) & ".xls")

However, I would like to incorporate the Autofilter selection criteria in the file name. I have identified that the code for the autofilter selection criteria is

 Selection.AutoFilter Field:=2, Criteria1:="Essbase"
    Selection.AutoFilter Field:=6, Criteria1:="High"

What I want to do is as follows:-

If Col A Criteria is "All" then goto Next Col until blank col is reached
If Col A Criteria is not "All" then concatenate it into a variable for use in the file name and then goto Next Col until blank col is reached.

Final result would be a file name of (using the above criteria as an example)

Essbase High 30 June 2004.xls

Many Thanks

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[xlbo]

Press F1
Enter "Autofilter" into the search criteria
Choose "Autofilter OBJECT"
Utilise the example that is provided as it does exactly what you have asked for

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Tiglet]

Geoff,

Thanks for the tip, unfortunately, I didn't understand what it was doing and still don't

Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub ChangeFilters()

Set w = Worksheets("Crew")
With w.AutoFilter
    currentFiltRange = .Range.Address
    With .Filters
        ReDim filterArray(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filterArray(f, 1) = .Criteria1
                    If .Operator Then
                        filterArray(f, 2) = .Operator
                        filterArray(f, 3) = .Criteria2
                    End If
                End If
            End With
        Next
    End With
End With

w.AutoFilterMode = False
w.Range("A1").AutoFilter field:=1, Criteria1:="S"

End Sub

Could you tell me what the f refers to (is that the variable) and what the 1 to 3 represents (is that the number of columns

Many Thanks

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[xlbo]

ok - you need to change the name of the worksheet to start with

The code is looping through all autofiltered columns and checking if there is a critera set. If there is, it is putting them into a 3 field array (thats what the 1 To 3 is doing)

f is merely a variable that is used to loop through the filtered columns - there are 3 fields in the array because you can have up to 2 criteria and an operator within each filter so the 1st part of the array is filled with the 1st criteria. If there is a 2nd criteria, the operator goes into the 2nd part of the array and the 2nd criteria goes in the 3rd. Easier way to explain is to show:
Create a new sheet called "Test" in your workbook and add this to the bottom of your code:

With Sheets("test")
For i = LBound(filterArray) To UBound(filterArray)
    For x = 1 To 3
        .Cells(i, x).Value = "'" & filterArray(i, x)
    Next x
Next i

Filter the original sheet and run the code you got from the help file (changing the sheet name as appropriate) with my new code tacked on the bottom - have a look at the results and try to understand what is happening in the code



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Tiglet]

Aha,

LBound and UBound are new to me but I guess they dynamically identify the furthest left col & furthest right col in the AutoFilter.

It made the drop down arrows disappear even though in the menu selection it is still shown as activated, so I commented out the following

w.AutoFilterMode = False

which seemed to solve it

The x in your extra code refers to the number of criteria options (I guess as when I increased it to 4 it gave an error!) and I can have as many criteria selected as I wish and it reports all of them with an equals sign prefix (Is it easy to get rid of that?) so I think I get that. I understand how you are writing the criteria to the cells in "test"

Am still bemused by this bit though

        ReDim filterArray(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filterArray(f, 1) = .Criteria1
                    If .Operator Then
                        filterArray(f, 2) = .Operator
                        filterArray(f, 3) = .Criteria2

although I think I understand that the f is referring to the criteria selected so if I did a coding equiv of old f value + new f value = essbasehigh using my original example, I would get concatenated selection criteria for the file name (Not the right syntax I'm sure), I don't really follow ReDim - I've looked at the Help [blue]The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).[/blue] but it's just not going in! Huge knowledge gap (verging on a chasm) I don't get why the size would change.
Does

.On

represent identifying if a criteria has been selected? and what does

                  filterArray(f, 1) = .Criteria1
                    If .Operator Then
                        filterArray(f, 2) = .Operator
                        filterArray(f, 3) = .Criteria2

do with the values of f that each line determines - are they getting written somewher or does f end up as a concatenation of the selections? (sorry, not sure I know how to establish a current value of f tried

ActiveCell.Value = f

but to no avail!
Many Thanks

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[xlbo]

Ok

You are correct that the autofiltermode = false removes the filters

f refers to filters - it is iterating through the filters collection of the autofilter object - it is just an integer value - like referencing

activesheet.autofilter.filters(1)

activesheet.autofilter.filters(2)

etc etc

lbound and ubound refer to the 1st and last element of an ARRAY. In this case, the array contains all the criteria and operators which are active in your autofilter

The bit with adding the values to the array:
With .Item(f) - references the column relatively from the start of the autofilter

If .On Then - test to see if the filter has been set on that column
filterArray(f, 1) = .Criteria1 - put the 1st criteria into the array
If .Operator Then - test to see whether there is more than 1 criteria set on that column
filterArray(f, 2) = .Operator - if there is, load the operator (ie AND or OR) into the array
filterArray(f, 3) = .Criteria2 - same goes for the 2nd criteria


The code I gave you will dump out the criteria values to the TEST worksheet as I explained. You are correct in that x = 1 to 3 references the maximum number of combinations of criteria and operator there can be (3) - that bit of code is taking the created array and dumping it out to a seperate sheet - you can use that bit to build your filename rather than dumping it out to another sheet

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Tiglet]

xlbo

Many thanks for your help (and patience) I'll have a play and give it a go

Have a star

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year