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

Knockoutpie

New Member
Joined
Sep 10, 2018
Messages
16
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?

VBA Code:
    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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 10, 2018
Messages
16
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!

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,934
Messages
5,627,697
Members
416,266
Latest member
stevenvanroeden

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
Top