Autofilter multiple sheets with manual date range

[jflo714]

I have a workbook that has multiple sheets from multiple linked datasources. I would like to have a Dashboard page that I am able to select a type a date range in B1 and C1 (start and end date) and it filter Column A in all of the other worksheets. I found the code below, but this looks at the Range(B1) and Range(C1) in their respective sheets not back to the "Dashboard" where the dates would have been set.

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20160623
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
xWs.Range("A1").AutoFilter 1, Criteria1:=">=Range(B1)", Operator:=xlAnd, Criteria2:="<=Range(C1)"
Next
End Sub


Thanks for time in advance

 

[JLGWhiz]

If your dashboad is on Sheet1, eg. then

xWs.Range("A1").AutoFilter 1, Criteria1:=">=[COLOR=#FF0000]Sheets("Sheet1").[/COLOR]Range(B1)", Operator:=xlAnd, _
Criteria2:="<=[COLOR=#FF0000]Sheets("Sheet1").[/COLOR]Range(C1)"

 

[jflo714]

If your dashboad is on Sheet1, eg. then

xWs.Range("A1").AutoFilter 1, Criteria1:=">=[COLOR=#FF0000]Sheets("Sheet1").[/COLOR]Range(B1)", Operator:=xlAnd, _
Criteria2:="<=[COLOR=#FF0000]Sheets("Sheet1").[/COLOR]Range(C1)"

Thanks for quick help.

This code is giving a compile error "Expected: end of statement" after the first Sheets("Sheet1").

I originally had that Sheet named "Dashboard" but was throwing a syntax error so I renamed it to Sheet 1 and getting that error above.

 

[Fluff]

Try

xWs.Range("A1").AutoFilter 6, Criteria1:=">=" & Sheets("Sheet1").Range("B1") & "", Operator:=xlAnd, _
Criteria2:="<=" & Sheets("Sheet1").Range("C1") & ""

 

[JLGWhiz]

Tx, @Fluff. good catch.

 

[jflo714]

Try

xWs.Range("A1").AutoFilter 6, Criteria1:=">=" & Sheets("Sheet1").Range("B1") & "", Operator:=xlAnd, _
Criteria2:="<=" & Sheets("Sheet1").Range("C1") & ""

That did it!

Thanks for the help, both of you!

 

[Fluff]

Glad we could help & thanks for the feedback