How do I open a workbook with the current month, Minus 1 day?

[Knockoutpie]

So I was able to get VBA to open the sheet name based on Month and Year (January 2020) sheet name for example, but if the month is currently December 1st, I don't want the December sheet, I want the November sheet because the data I'm using is November 30th. We will be working on the last line of code.. A Yesterday function would be great

Is there any way I can use the Now or Today function minus 1 day? Or can you help advise a way to do this?

    Dim folderPath As String, tableName As String, latestTblName As String
Dim latestModified As Date, modifiedDate As Date

folderPath = "Insert Path Name Here"

tableName = Dir(folderPath & "*.xlsx")

Do While tableName <> vbNullString
    modifiedDate = FileDateTime(folderPath & tableName)
    If latestModified < modifiedDate Then
        latestModified = modifiedDate
        latestTblName = tableName
    End If
    tableName = Dir()
Loop

Workbooks.Open folderPath & latestTblName
    
' can we open a file based on the month?

Sheets(UCase(Format(Today(), "mmmm YYYY"))).Select

 

[Rick Rothstein]

I would think you only have to subtract 1 from the Today() function in your last posted line of code...

Sheets(UCase(Format(Today() - 1, "mmmm YYYY"))).Select

 

[Knockoutpie]

I would think you only have to subtract 1 from the Today() function in your last posted line of code...

Sheets(UCase(Format(Today() - 1, "mmmm YYYY"))).Select

Thanks Rick, I tried that every possible way, Excel doesn't like it with Today, but it will let me do it with Now. Cheers!

Sheets(UCase(Format(Now() - 1, "mmmm YYYY"))).Select

 

[Rick Rothstein]

I wasn't thinking when I accepted your use of the Today function in your line of VBA code.? VBA has a Date function for just the date and the Now function for the date and time... either will work for the usage you are looking for.