Select a worksheet using today's date

Mikeymike15

New Member
Joined
Jul 20, 2006
Messages
33
Hi guys,

My question is:

I'd like my macro to preferably select the worksheet it draws data from, based on today's date.

My workbook is updated every day with a new tab and a new set of data, so on July 2nd, I create a new sheet entitled "0702", however right now I have to go into the macro and adjust the date. I currently have another table that has a date reference (i.e. 7/2/15 which gets converted to 0702)

Thus, below, where it states Sheets("0702").select, I'd like the macro to find sheet 0702 and pick out the tab without me having to manually re-adjust the coding.

Alternatively, I wouldn't mind simply having a pop-up box ask for today's date, which would simplify my steps.

Thanks for any help,
Mike


Sub CMIShort1()

' ***************************************
' ***** CMI Desk PO Shortage Report *****
' ***************************************
' Searches for Matl Help required and pulls back every row to create a report showing oustanding PO's

Dim lastrow As Long
Dim Li As Long

lastrow = Worksheets("CMIShort").Range("A2500").End(xlUp).Row
Sheets("0702").Select
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,277
Office Version
  1. 365
Platform
  1. Windows
This will do it:

Code:
Dim mySheetName As String
Dim mySheet As Worksheet

mySheetName = Format(Month(Now), "00") & Format(Day(Now), "00")
Set mySheet = Sheets(mySheetName)

mySheet.Select
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,380
Office Version
  1. 2019
Platform
  1. Windows
try:

Code:
Sheets(Format(Date, "mmdd")).Select

Dave
 

digidave

New Member
Joined
Apr 25, 2014
Messages
13
Try this:

Code:
Dim str As String
str = InputBox("Please insert the date in format MMDD:", "Insert Date")

Sheets(str).Select
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,813
Members
414,260
Latest member
joishe

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