Can someone help me with an advanced filter (AF) issue. I have 2 AF on one page pulling from a different page sources. I create the macros on the page where the data is to be returned, I get an error of "No Match Found" when I run the filter macros in the userform unless I am physically on the extract range page of the workbook. If I click off to another tab, such as the source worksheet, and call the userform from the VBA Editor, I get the "No Match Found" error box. When I click back onto the tab that the data is returned to (Sheet10), and call the userform, I get sorted data in my form?
Here is the AF code I am running. Criteria and extract is the same page for both filters, returning data from different worksheets in my workbook, to one worksheet in different tables. I am on the extract range page when recording my macros to a different location. This is likely a newb question, but I do not know what I need to do to make this work.
Adv1ListBox1
Table735 Source = Sheet7.Range("Table735[#All]")
Table1 Criteria = Sheet10.Range("E4:E5")
Table1 Extract Range = Sheet20.Range("A7:E7")
Adv2ListBox2
Table18 Source = Sheet20.Range("Table18[#All]")
Table2 Criteria = Sheet10.Range("I4:I5")
Table2 Extract Range = Sheet20.Range("G7:J7")
Here is the AF code I am running. Criteria and extract is the same page for both filters, returning data from different worksheets in my workbook, to one worksheet in different tables. I am on the extract range page when recording my macros to a different location. This is likely a newb question, but I do not know what I need to do to make this work.
Adv1ListBox1
Table735 Source = Sheet7.Range("Table735[#All]")
Table1 Criteria = Sheet10.Range("E4:E5")
Table1 Extract Range = Sheet20.Range("A7:E7")
Adv2ListBox2
Table18 Source = Sheet20.Range("Table18[#All]")
Table2 Criteria = Sheet10.Range("I4:I5")
Table2 Extract Range = Sheet20.Range("G7:J7")
VBA Code:
Sub AdvFilterLB1()
Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("Sheet2!Criteria"), CopyToRange:=Range( _
"Sheet2!Extract"), Unique:=False
End Sub
Sub AdvFilterLB2()
Sheet20.Range("Table18[#All]").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheet10.Range("I4:I5"), CopyToRange:=Range("G7:J7"), Unique:=False
End Sub