Need VBA code to roll when I roll date in file

See if this works for you

ChDir "\\ict0ms06\ntroacct\Trading\TSR Risk Reports\Consolidated Fertilizer"
Dim dt As Variant
 Workbooks.Open Filename:= _
 "\\ict0ms06\ntroacct\Trading\TSR Risk Reports\Consolidated Fertilizer\RiskCalcMasterNewReporting-NEW.xlsx"
 Sheets("Daily PNL").Select
dt = Format(Date, "yymmdd")
Windows("Daily Position " & dt & ".xlsm").Activate
 Range("G22:H36").Select
 Selection.Copy
 Windows("RiskCalcMasterNewReporting-NEW.xlsx").Activate
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Sheets("Send to TSR").Select
 Range("A1").Select
 Application.CutCopyMode = False
 ActiveWorkbook.Save
 ActiveWindow.Close
 Range("A1").Select
 End Sub

Originally Posted by scrivenk

The issue with that is I am always reporting on a day behind schedule. So my file will not be the same as the current day. Is there a way to reference a cell in the worksheet?

Would

dt = Format(Date - 1, "yymmdd")

not work? If Not, and you want to use a range for the date, then just substitute the range reference for 'Date' and enter your date in short date format in that range.

Originally Posted by scrivenk

This wouldn't work. Today's date was the 29th, but I was reporting for Friday which was the 26th. So I guess said a better way is I am always reporting one workday behind.

To use a range where you enter the date for the report, assume Range("A1")
Change this line

dt = Format(Date, "yymmdd")

To

dt = Format(Range("A1").Value, "yymmdd")

For a different cell, just change the A1 to whatever cell you use.