VBA to filter by user specified criteria

[kenadams378]

Morning,

Is it possible to write code so that it filters data specifically on what the user wants to filter by.

For example.

If the user specified filter by column 19, GBP?

[arlu1201]

How would the user be specifying the criteria?

[kenadams378]

by entering a column number and then criteria

For example

column = 19
criteria = GBP.

[arlu1201]

Where would the criteria be specified?

[darayuk]

It is possible. You may read this page to get the idea:http://www.worldbestlearningcenter.c...ta-example.htm

[kenadams378]

On a front sheet called 'start'

Cell A1, A2

[arlu1201]

Try this

Dim lrow As Long
    
    lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Sheet1").Range("$A$1:$Z$" & lrow).AutoFilter Field:=Worksheets("Start").Range("A1").Value, Criteria1:=Worksheets("Start").Range("A2").Value

[kenadams378]

Thanks Arlu,

The following code

.AutoFilter Field:=Worksheets("Start").Range("O13").Value, Criteria1:=Worksheets("Start").Range("O14").Value, Criteria2:=Worksheets("Start").Range("O15").Value
            .Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")

Can you check if this is okay, i get an error message to say subscript out of range

[arlu1201]

Subscript out of range has everything to do with the sheet names.

Just check that your sheet names are named properly and matching with the code in terms of case and spelling.

[kenadams378]

checked and names are okay, the only thing i can think of is that does it need to be Auto Filter Column rather than field?!

[arlu1201]

No the syntax says field.

You can try recording the code with some temp fields and then change them accordingly to your start sheet fields.

[kenadams378]

Still not working, i have attached the workbook here, can you have a look for me, the filter is applied as part of step 1?

[kenadams378]

Have you been able to look at my attachment Arlu?

[kenadams378]

In the following line of code i have the error 'object required'

Any ideas on what the issue is;

            .AutoFilter Field:=Worksheets("Start").Range("N13").Value, Criteria1:=Worksheets("Start").Range("N14").Value, Criteria2:=Worksheets("Start").Range("N15").Value

[arlu1201]

What is the code before that line?

[kenadams378]

 With wb.Worksheets("Sheet1")
       lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A1:DC" & lrow)

[arlu1201]

 With wb.Worksheets("Sheet1")
       lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A1:DC" & lrow)

What is wb declared as in the dim statements?

[kenadams378]

It is declared as Workbook?

Dim wb As Workbook

[arlu1201]

Can you post your whole code here? It needs a lil change.

[kenadams378]

Sub Bevel1()
    Dim lrow As Long
    Dim FName As Variant
    Dim wb As Workbook
    Dim vSheets
    Dim vSheet
    Dim lRows As Long

    'Turn off settings that slow down the macro process'
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    vSheets = Array("Morca", "Moeca")

    ChDir "\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\"

    For Each vSheet In vSheets
        'Prompt user for most recent MOECA/MORCA Liquidity Data filepath'

        FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", _
                                            Title:="Please open the most recent " & vSheet & " Data file")

        If FName = "False" Then
            MsgBox "You have not selected a file."
            Exit Sub
        End If

        Set wb = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True)

        'Using the filepath, select original data sheet and clear any old data from the sheet'
        'Select the range of data from the liquidity data and paste this into the original data tab'
        'Repeat using array'
        
        ThisWorkbook.Worksheets("Original " & vSheet).Cells.ClearContents
        With wb.Worksheets("Sheet1")
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A1:P" & lrow).Copy ThisWorkbook.Worksheets("Original " & vSheet).Range("A1")
        End With
        wb.Close False
    Next vSheet

        'Prompt user for most recent S29 file'

    FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", _
                                        Title:="Please open the most recent S29 Data file")

    If FName = "False" Then
        MsgBox "You have not selected an S29 file."
        Exit Sub
    End If

        'Once file is open, clear data from S29 Data sheet and copy range from FName, filter using specfic criteria on
        'Field 19 (Stock) and paste to S29 Data sheet in Cell A1

    Set wb = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True)

    ThisWorkbook.Worksheets("S29 Data").Cells.ClearContents
    
    With wb.Worksheets("Sheet1")
       lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A1:DC" & lrow)
            .AutoFilter Field:=19, Criteria1:="GBPMORCA", Operator:=xlOr, Criteria2:="GBPMOECA"
            .Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")
        End With
        wb.Close False
         
        'Add username, date & time of the last time the macro was used'

With Sheets("Start")
    .Cells(31, 2) = Now
    .Cells(32, 2) = CreateObject("WScript.Network").UserName
    End With
    
End With
End Sub

[arlu1201]

Try this

Option Explicit

Sub Bevel1()
    Dim lrow As Long
    Dim FName As Variant
    Dim wb As Variant
    Dim vSheets
    Dim vSheet
    Dim lRows As Long

    'Turn off settings that slow down the macro process'
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    vSheets = Array("Morca", "Moeca")

    ChDir "\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\"

    For Each vSheet In vSheets
        'Prompt user for most recent MOECA/MORCA Liquidity Data filepath'

        FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", _
                                            Title:="Please open the most recent " & vSheet & " Data file")

        If FName = "False" Then
            MsgBox "You have not selected a file."
            Exit Sub
        End If

        Set wb = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True)

        'Using the filepath, select original data sheet and clear any old data from the sheet'
        'Select the range of data from the liquidity data and paste this into the original data tab'
        'Repeat using array'
        
        ThisWorkbook.Worksheets("Original " & vSheet).Cells.ClearContents
        With Workbooks(wb).Worksheets("Sheet1")
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A1:P" & lrow).Copy ThisWorkbook.Worksheets("Original " & vSheet).Range("A1")
        End With
        Workbooks(wb).Close False
    Next vSheet

        'Prompt user for most recent S29 file'

    FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", _
                                        Title:="Please open the most recent S29 Data file")

    If FName = "False" Then
        MsgBox "You have not selected an S29 file."
        Exit Sub
    End If

        'Once file is open, clear data from S29 Data sheet and copy range from FName, filter using specfic criteria on
        'Field 19 (Stock) and paste to S29 Data sheet in Cell A1

    Set wb = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True)

    ThisWorkbook.Worksheets("S29 Data").Cells.ClearContents
    
    With Workbooks(wb).Worksheets("Sheet1")
       lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A1:DC" & lrow)
            .AutoFilter Field:=19, Criteria1:="GBPMORCA", Operator:=xlOr, Criteria2:="GBPMOECA"
            .Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")
        End With
        Workbooks(wb).Close False
         
        'Add username, date & time of the last time the macro was used'

With Sheets("Start")
    .Cells(31, 2) = Now
    .Cells(32, 2) = CreateObject("WScript.Network").UserName
    End With
    
End With

With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

[kenadams378]

Thanks but i have an error on the same line

Subscript out of range

The only sheet this effects is the start sheet and that is named the same as the macro

[arlu1201]

Is it this line that is affected? -

With Workbooks(wb).Worksheets("Sheet1")

Does your file have a Sheet1? The file that you are opening through the macro?