Filter Data on One Sheet by a Date Value on a Different Sheet

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
701
Office Version
  1. 365
Platform
  1. Windows
I have 2 sheets. One named LM and the other named D. I'm trying to filter the data in column B on sheet D for values that match the value in cell B3 on the LM sheet. The data in column B of the D sheet and the value in B3 of the LM sheet are m/d/yyyy values formatted as mmm-yy. When I run this code, the first filter line runs fine, but the second one filters all of the data out even though there are about 15 records that should be filtered to. It's probably something simple, but I haven't been able to find anything online that successfully explains how to achieve the goal.

VBA Code:
Sub CreateViews()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mD As Worksheet, mLM As Worksheet
Dim rDate As Date
Dim mDLR As Long, mLMLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("D")
Set mLM = m.Sheets("LM")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row
mLMLR = mLM.Range("A" & Rows.Count).End(xlUp).Row

'Removes filters from the working data if any exist.
If mD.AutoFilterMode Then mD.AutoFilterMode = False

'Unhides any columns and rows that may be hidden on the working data.
With mD.UsedRange
    .Columns.EntireColumn.Hidden = False
    .Rows.EntireRow.Hidden = False
End With

mD.Activate

With mD.Range("A1:X" & mDLR)
    .Sort Key1:=.Columns("I"), Order1:=xlAscending, _
            Key2:=.Columns("G"), Order1:=xlAscending, _
            Key3:=.Columns("K"), Order1:=xlAscending, _
            Orientation:=xlTopToBottom, Header:=xlYes
End With

rDate = mLM.Range("B3").Value

With mD.Range("A1:X" & mDLR)
    .AutoFilter Field:=9, Criteria1:="LM", Operator:=xlFilterValues
    .AutoFilter Field:=2, Criteria1:="=" & rDate, Operator:=xlFilterValues
End With
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top