AsgiE
New Member
- Joined
- May 26, 2005
- Messages
- 44
Greetings,
I am building a front end filter page where users can select multiple filters using Combo Boxes linked to a database of information in a tab.
I am then using VBA to extract the filtered database to another file.
I have used and IF, Then, Else approach to cycle through the filters to check if there is a filter to apply in the series of filtered cells. The idea being that the user can choose any or all the filters and the extract will work.
I am encountering and "Object Missing" error in the code below highlighted in blue.
Sub Filterandcopyandpasteresults()
'
' Filterandcopyandpasteresults Macro
' Macro which will filter on Discipline and other filters then copy and paste the results in a seperate sheet
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Sheets("Data").Select
If Range(Filters!b1) > 1 And Range(Filters!c1) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=3, Criteria1:=Range("Filters!c2")
Else
If Range(Filters!b9) > 1 And Range(Filters!c9) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=5, Criteria1:=Range("Filters!c9")
Else
If Range(Filters!b13) > 1 And Range(Filters!c13) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=7, Criteria1:=Range("Filters!c13")
Else
If Range(Filters!b17) > 1 And Range(Filters!c17) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=8, Criteria1:=Range("Filters!c17")
Else
If Range(Filters!b21) > 1 And Range(Filters!c21) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=9, Criteria1:=Range("Filters!c21")
Else
If Range(Filters!b25) > 1 And Range(Filters!c25) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=10, Criteria1:=Range("Filters!c25")
End If
End If
End If
End If
End If
End If
Columns("A:R").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveWorkbook.ActiveSheet.Name = "Results"
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Workbooks("Prescription Report - MASTER - 2017.12.13").Activate
Sheets("Data").Select
Range("$A$1:$N$2000").AutoFilter Field:=3
Range("$A$1:$N$2000").AutoFilter Field:=5
Range("$A$1:$N$2000").AutoFilter Field:=7
Range("$A$1:$N$2000").AutoFilter Field:=8
Range("$A$1:$N$2000").AutoFilter Field:=9
Range("$A$1:$N$2000").AutoFilter Field:=10
Range("A2").Select
Application.CutCopyMode = False
Range("C2").Select
End Sub
Is there a better method to cycle through the filter selection.
The rest of the code (which still needs some updating) works as expected.
Any help will be greatly appreciated.
Thanks
Regards
Asgi E
I am building a front end filter page where users can select multiple filters using Combo Boxes linked to a database of information in a tab.
I am then using VBA to extract the filtered database to another file.
I have used and IF, Then, Else approach to cycle through the filters to check if there is a filter to apply in the series of filtered cells. The idea being that the user can choose any or all the filters and the extract will work.
I am encountering and "Object Missing" error in the code below highlighted in blue.
Sub Filterandcopyandpasteresults()
'
' Filterandcopyandpasteresults Macro
' Macro which will filter on Discipline and other filters then copy and paste the results in a seperate sheet
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Sheets("Data").Select
If Range(Filters!b1) > 1 And Range(Filters!c1) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=3, Criteria1:=Range("Filters!c2")
Else
If Range(Filters!b9) > 1 And Range(Filters!c9) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=5, Criteria1:=Range("Filters!c9")
Else
If Range(Filters!b13) > 1 And Range(Filters!c13) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=7, Criteria1:=Range("Filters!c13")
Else
If Range(Filters!b17) > 1 And Range(Filters!c17) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=8, Criteria1:=Range("Filters!c17")
Else
If Range(Filters!b21) > 1 And Range(Filters!c21) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=9, Criteria1:=Range("Filters!c21")
Else
If Range(Filters!b25) > 1 And Range(Filters!c25) <> 0 Then
Range("$A$1:$r$2000").AutoFilter Field:=10, Criteria1:=Range("Filters!c25")
End If
End If
End If
End If
End If
End If
Columns("A:R").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveWorkbook.ActiveSheet.Name = "Results"
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Workbooks("Prescription Report - MASTER - 2017.12.13").Activate
Sheets("Data").Select
Range("$A$1:$N$2000").AutoFilter Field:=3
Range("$A$1:$N$2000").AutoFilter Field:=5
Range("$A$1:$N$2000").AutoFilter Field:=7
Range("$A$1:$N$2000").AutoFilter Field:=8
Range("$A$1:$N$2000").AutoFilter Field:=9
Range("$A$1:$N$2000").AutoFilter Field:=10
Range("A2").Select
Application.CutCopyMode = False
Range("C2").Select
End Sub
Is there a better method to cycle through the filter selection.
The rest of the code (which still needs some updating) works as expected.
Any help will be greatly appreciated.
Thanks
Regards
Asgi E