How to increase the date in a cell for each new workbook

[GARTHMAN]

I am creating a VBA macro that will create an identical workbook for every day of the year. It works great with one exception... the workbook names are created and saved successfully, but there are 2 cells that need to update in each of the workbooks. This is a no brainer I'm sure, but I'm missing the parameter placement that will call out the cell that needs changed. That should automatically update the 2nd cell based on a formula in the workbook. Following is the code and an image of the cells that need to change
----------------
Option Explicit

Sub Create_Multiple_Workbooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = False
'
Dim i As Long
Dim wb As Workbook
Dim fec1 As Date, fec2 As Date
Dim l1 As Workbook, h1 As Worksheet
Dim ruta As String, mes As String, ruta2 As String, arch As String
Set l1 = ThisWorkbook
Set h1 = l1.Sheets("Ops-Fleetwatch-GFI Comparison") 'name of template sheet
'
ruta = l1.Path & "\"
fec1 = DateSerial(2021, 7, 2)
fec2 = DateSerial(2022, 6, 30)
For i = fec1 To fec2
Application.StatusBar = "Creating file : " & i
mes = Format(i, "MMM")
If Dir(ruta & mes & "\") = "" Then
MkDir (ruta & mes)
End If
ruta2 = ruta & mes & "\"
arch = " Operations-Fleetwatch-GFI Daily Vehicle Log" & Format(i, "MM-DD-YYYY")

ActiveWorkbook.Sheets.Copy

Set wb = ActiveWorkbook
wb.SaveAs Filename:=ruta2 & arch & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
wb.Close False
Next
Application.StatusBar = False
MsgBox "End"

End Sub

B1 needs to increase by 1 day and my existing formula will automatically update the value in cell E4.

Thanks in advance.

 

[Osvaldo Palmeiro]

Please add the red line to the code, as below. I assumed the cell to be updated is B4, as showed in the image.

ActiveWorkbook.Sheets.Copy
[B4] = [B4] + 1
Set wb = ActiveWorkbook

 

[GARTHMAN]

Osvaldo,

Thank you. the formula works as it should based on what I shared. However, perhaps you know a quick fix for this issue . "Ops-Fleetwatch-GFI Comparison" is my original workbook being called. The 1st workbook created is "Ops-Fleetwatch-GFI Comparison 07/01/2021". I need the successive loops through the code to call out the workbook that was just created. In other words, I want your formula to update based on the previous workbook created. Otherwise, each successive workbook is renamed correctly but the formula is always updating based on the original workbook and it always shows as "Thursday, 7/1/2021 4:00:00 AM" through "Friday, 7/2/2021 3:59:59 AM". Your input is appreciated.


Option Explicit

Sub Create_Multiple_Workbooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = False
'
Dim i As Long
Dim wb As Workbook
Dim fec1 As Date, fec2 As Date
Dim l1 As Workbook, h1 As Worksheet
Dim ruta As String, mes As String, ruta2 As String, arch As String
Set l1 = ThisWorkbook
Set h1 = l1.Sheets("Ops-Fleetwatch-GFI Comparison") 'name of template sheet'
ruta = l1.Path & "\"
fec1 = DateSerial(2021, 7, 1)
fec2 = DateSerial(2022, 6, 30)
For i = fec1 To fec2
Application.StatusBar = "Creating file : " & i
mes = Format(i, "MMM")
If Dir(ruta & mes & "\") = "" Then
MkDir (ruta & mes)
End If
ruta2 = ruta & mes & "\"
arch = "Operations-Fleetwatch-GFI Daily Vehicle Log " & Format(i, "MM-DD-YYYY")

ActiveWorkbook.Sheets.Copy

Set wb = ActiveWorkbook
wb.SaveAs Filename:=ruta2 & arch & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
wb.Close False
Next
Application.StatusBar = False
MsgBox "End"

End Sub

 

[Osvaldo Palmeiro]

Hello.
If I correctly understood what you meant it seems that the date you want to be placed in B4 of each new file is the same date placed by the code at the end of that new file name itself.

If so, then you could use the value from the variable "i" in place of the expression I offered before:

ActiveWorkbook.Sheets.Copy
[B4] = i
'or [B4]= i+1 ~~~> if you want one day ahead of i date
Set wb = ActiveWorkbook

 

[GARTHMAN]

You are AWESOME! Thank you very much!