Good afternoon!
So I am trying to sat up an automatic report for my job where I can use advance filters to type the selected months into the criteria page and have it filter per those months. The macro however uses both Advanced and Normal filters and when I transition between, the entire table is reset and I lose the advanced filter. The code is below with the annotation at spot where things start to go screwy.
Sub MonthSelectExternal()
'
' MonthSelectExternal Macro
' This runs the month(s) of your choice. Saved in an external workbook to increase processing speed. Will change later.
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveSheet.ShowAllData
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("Table2[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Sheets("MonthSelect").Range("A1:M4"), Unique:=False
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
Sheets("Report Generation").Select
'Below is where the filters revert back and make the advance filter inactive.
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=4, Criteria1:= _
"ST"
Range("Table2[[#Headers],[Order Type]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Any ideas on how to keep the advance filter settings would be greatly Helpful!!
So I am trying to sat up an automatic report for my job where I can use advance filters to type the selected months into the criteria page and have it filter per those months. The macro however uses both Advanced and Normal filters and when I transition between, the entire table is reset and I lose the advanced filter. The code is below with the annotation at spot where things start to go screwy.
Sub MonthSelectExternal()
'
' MonthSelectExternal Macro
' This runs the month(s) of your choice. Saved in an external workbook to increase processing speed. Will change later.
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveSheet.ShowAllData
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("Table2[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Sheets("MonthSelect").Range("A1:M4"), Unique:=False
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
Sheets("Report Generation").Select
'Below is where the filters revert back and make the advance filter inactive.
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=4, Criteria1:= _
"ST"
Range("Table2[[#Headers],[Order Type]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Any ideas on how to keep the advance filter settings would be greatly Helpful!!