Refer to sheet in VBA that is named for yesterday's date

[pattik77]

I have a workbook where I create a new sheet daily, Monday through Friday and it is named for the current date in ddmmm format. I run a macro to format the sheet like I want it. I also need to pull some data from the prior day's worksheet. Can anyone tell me how to refer to the sheet name for yesterday's worksheet and on Monday to refer to Friday's sheet?

Thanks in advance,
Patti

 

[pattik77]

I apologize for now being clear. I need to add that reference in the VBA code, not in a formula.

 

[Rick Rothstein]

I have a workbook where I create a new sheet daily, Monday through Friday and it is named for the current date in ddmmm format. I run a macro to format the sheet like I want it. I also need to pull some data from the prior day's worksheet. Can anyone tell me how to refer to the sheet name for yesterday's worksheet and on Monday to refer to Friday's sheet?

This will reference yesterday's worksheet...

Sheets(Format(WorksheetFunction.WorkDay_Intl(Date, -1, 1), "ddmmm"))

 

[pattik77]

Works perfectly, thanks.