When I open the workbook, a new sheet is created using today's date as the name e.g. 18-July (The WorksheetFunction.Text(DateToday, "[$-C0C]dd-mmmm") is so that the name would be in french). In the same script, I copy the content of a template onto the new sheet. Where I am having trouble is with three cells B39:B41 where I need these cells to refer to the cells D39:D41 from the previous day i.e. the sheet named 17-July. ='17-July'!D39. Here is the script that I have so far. Any help would be appreciated.
Private Sub Workbook_Open() Dim TodaysDate As String Dim NewSheetName As String Dim DateToday As String 'Get Todays Date DateToday = Now() NewSheetName = WorksheetFunction.Text(DateToday, "[$-C0C]dd-mmmm") 'Check Todays Sheet Doesn't Already Exist Dim SheetExists As Boolean For sheetnames = Worksheets.Count To 1 Step -1 If Worksheets(sheetnames).Name = NewSheetName Then SheetExists = True Exit For End If Next If SheetExists = False Then Sheets.Add(Before:=Sheets(Sheets.Count)).Name = NewSheetName Sheets("demo").Range("A1:Z100").Copy Destination:=ActiveSheet.Range("A1:Z100") End If End Sub