VBA to Get Maximum Value based on a Date in Another Column

[OilEconomist]

Thanks in advance for your suggestions. If there is an alternative quicker method, by all means, please let me know.

I will explain in simple terms and then expand for my case:

Simple Example:
• I have dates in Column B of sheet “WL.Date” and I am attempting to retrieve corresponding data from sheet “Main” and input it into the cells of Column E.
• Within sheet “Main” Column A has numerical values and Column B has dates.
• For “WL.Date” I loop through Column E and where there is no data, I obtain it from “Main”
• I store the corresponding date for the cell with the missing data (“WL.Main” tab).
• I then go to sheet “Main” and retrieve the maximum value from Column A which corresponds to the date in Column B that was stored from “WL.Date”.


Actual:
• I have a sheet “WL.Date” with a list of dates in the format of “YYYY-MM-DD, DDD” (e.g. 2021-02-03, Wed) in column B.
• I have five columns where the heading of each column corresponds to the sheet name where I want to obtain data for the respective date in each column.
• The heading titles/sheet names, which are in Row 6, are:
Cell E6: : “Main”
Cell F6: “Scalps”
Cell G6: “Backburners”
Cell H6: “Sympathy”
Cell I6: “All”
Within “WL.Main” I store the first heading/sheet name in the string “SheetName” where it starts with “Main” in Column E.
• I loop starting in Column E from the first blank row to the last row.
• I store the corresponding date for the first blank cell and go to “Main” and filter on Column B for that date.
• I then get the maximum value in Column A.
• I repeat the above step for every date where the value in Column E is missing.
• I then repeat for the four remaining columns.
• Below is the code I have written thus far.
• I believe what is happening is that it filters on the date and finds nothing because the date is not being transferred correctly. I get the error: “Run-time error ‘13’: Type mismatch”

Sub WL_Menu_Stats()

'________________________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
    'Turn off Display Alerts
        Application.DisplayAlerts = False

    'Turn off Screen Update
        Application.ScreenUpdating = False

    'Turn off Automatic Calculations
        Application.Calculation = xlManual



'________________________________________________________________________________________________________
'Dimensioning
   
    'Dim long
        Dim LastRowStart As Long
        Dim LastRowEnd As Long
        Dim LastRow As Long
       
        Dim i As Long
        Dim j As Long
       
        Dim Count As Long
       
   
    'Dim Strings
        Dim SheetName As String
       
    'Dimensioning Date
        Dim Date_WL As Long
       
    'Dimensioning Range
        Dim RangeMax As Range
       
        Dim Maximum As Double
   
'________________________________________________________________________________________________________
'Code

    'Activate the sheet "WL.Menu"
        Worksheets("WL.Menu").Activate

    'Find the LastRow
        LastRowEnd = Cells(Rows.Count, 3).End(xlUp).Row
       
    'Start a loop to get the values
        For i = 5 To 9
       
        'Find the starting row by finding the last row in the column
                     LastRowStart = Cells(Rows.Count, i).End(xlUp).Row + 1

        'This gets the sheet name where the data needs to be obatined.
            SheetName = Cells(6, i).Value
   
            'This loops through the sheet for the first column (columns E through I)
                For j = LastRowStart To LastRowEnd
                   
                    Date_WL = Range("B" & j).Value 'Stores the data that needs to be looked up
                   
                    Worksheets(SheetName).Activate 'Activate the sheet where the filter needs to be applied _
                        does this step need to be done?
                   
                    Date_WL = Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")
                    MsgBox Date_WL
                   
                    Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= _
                    Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")
                  
                    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
                   
                    Set RangeMax = Worksheets(SheetName).Range("A7:A" & LastRow)
                    Maximum = Application.WorksheetFunction.Max(RangeMax)
                   
                   
                    Worksheets("WL.Menu").Activate
                    Cells(j, i).Value = Maximum
                   
           
                Next j
              
        Next i
   





'________________________________________________________________________________________________________
'XX. Turn on alerts, screen updates, and calculate

        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate


         
 
    End Sub

 

[mart37]

Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= Date_WL

 

[OilEconomist]

Worksheets(SheetName).Range("A6").AutoFilter Field:=2, Criteria1:= Date_WL

Thanks @mart37 for your response, but when I make that change I get the following error

"Run-time error '13': Type Mismatch" on the following line:

Date_WL = Format(CDate(Date_WL), "YYYY-MM-DDD, DDD")

 

[mart37]

"YYYY-MM-DDD, DDD" ==>"YYYY-MM-DD, DDD"

 

[mart37]

    Dim Date_WL As String
    Date_WL = Format(CDate(Range("B" & j).Value), "YYYY-MM-DD, DDD")
    ActiveSheet.Range("A6").AutoFilter Field:=2, Criteria1:=Date_WL