I am working on a macro program for this sheet of information. It should filter by Completion Date and blanks, as well as filtering by Status. The statuses I want are In Queue and Completed. This should result in a sheet that shows me what was completed on the date Input, what’s In Queue, as well as blanks which will let me know that a batch was not completed today.

I’ve made a macro that sends the current active sheet as an email to the team, that doesn’t need editing. However, for some reason, the macro to filter doesn’t work as intended. I would like to assign the macro made to the “Generate Report” button. Note that I want the Complete Date to be filtered, not the Check-in Date.

Please let me know if you can help.

Thanks! –> Link to the file on Google Drive

-J

Here is the code for the email portion as well as my current code for the filters. I’ve deleted and rewritten so it’s not the best. I’ve tried autofilter but unfortunately it keeps filtering by check in date not completion date, and it keeps trying to find “In Queue” inside the check in date column.

The name of the first sheet is MasterLog

Sub filter_multiple()

With Sheets("MasterLog").Range("C3")
    .AutoFilter field:=1, Criteria1:=Range("C1").Value
    .AutoFilter field:=1, Criteria1:=Range("I1").Value
    End With
    


With Sheets("MasterLog").Range("E3")
    .AutoFilter field:=1, Criteria1:=Range("P1").Value
    .AutoFilter field:=1, Criteria1:=Range("Q1").Value
    
End With

    
End Sub

Sub EmailActiveSheet()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String
    Dim ws As Worksheet
    Dim TempWB As Workbook
    
    ' Set the worksheet you want to send
    Set ws = ThisWorkbook.Sheets("MasterLog") 'Replace "SheetName" with your sheet's name
    
    ' Create a temporary copy of the sheet as a new workbook
    ws.Copy
    Set TempWB = ActiveWorkbook
    
    ' Define the temporary directory
    TempFilePath = Environ$("temp") & ""
    
    ' Check if the temporary directory exists, and create it if not
    If Len(Dir(TempFilePath, vbDirectory)) = 0 Then
        MkDir TempFilePath
    End If
    
    TempFileName = "TempSheet"
    FileFullPath = TempFilePath & TempFileName & ".xlsx"
    
    ' Save the temporary workbook
    TempWB.SaveAs FileFullPath, FileFormat:=51 ' 51 represents the xlsx file format
    TempWB.Close False
    
    ' Create a new Outlook instance
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0) ' 0 represents a mail item
    
    ' Compose the email
    With OutMail
        .To = "[email protected]" ' Replace with the recipient's email address
        .Subject = "Email Macro Test" ' Replace with your email subject
        .Body = "Hello, please find the attached sheet." ' Replace with your email body
        
        ' Attach the specific sheet
        .Attachments.Add FileFullPath
        
        ' Uncomment the next line to display the email before sending (for testing)
         .Display
        
        ' Uncomment the next line to send the email immediately
        ' .Send
    End With
    
    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    ' Delete the temporary file
    Kill FileFullPath
    
End Sub