Copy formulas from one sheet to another

[JARichard74]

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

 

[RobP]

Hi, this seems to be able to reference what you need. Only caveat is that when you open your workbook, It expects yesterdays sheet to be active. eg. before you actually add a new sheet to it.

First store the sheet name you used yesterday - you can put this just after you dimension your variables.

yesterdayssheet = ActiveSheet.Name

Then, use this to copy the data from cells D of the old sheet into Cells B of your new one. You can add this line to the end of your code.

ActiveSheet.Range("b39:b41") = Sheets(yesterdayssheet).Range("d39:d41").Value

When I tried your code, it was taking exception to your new "sheetname" when you were creating the sheet, but I assume you had that working on your machine given the problem you mentioned above ...
(it appears you cannot use / or : for a sheetname...)

Rob

 

[JARichard74]

Thanks Rob. Works well with the assumption yesterdays sheet is active when the workbook is open. That will likely be true for 99% of the time. How would I make yesterdayssheet always today - 1 day e.g. if today is 19-July how would I make yesterdayssheet sheet.name is 18-July and it keeps rolling like that till the end of the month.

 

[JARichard74]

I added the following code and it seems to work. DateHier is yesterday's date in french...

Dim DateHier As String
DateHier = Now() - 1
yesterdayssheet = WorksheetFunction.Text(DateHier, "[$-C0C]dd-mmmm")

 

[RobP]

Great - thanks for the feedback - glad to have been of assistance.
cheers
Rob