Select a worksheet using today's date

[Mikeymike15]

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

 

[steve the fish]

This will do it:

Dim mySheetName As String
Dim mySheet As Worksheet

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

mySheet.Select

 

[dmt32]

try:

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

Dave

 

[digidave]

Try this:

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

Sheets(str).Select

 

[Mikeymike15]

Thank you both....

those did the trick....