Calculation in between generated worksheets

[Udish12]

Hi,
I generate for every month a workbook containing a daily worksheet using a VBA that I copied from the internet and is working great. It asks for the month/year and generates the daily worksheets for that month. I'm attaching the VBA macro.

Is there a way to include formulas on the generated worksheets that will make calculations (in between those worksheets) based on the results of a previous day? For example, cell A5 on day2 will be the sum of A1:A4 from day1?

I know how to access the previous day, and how to do the A1:33 SUM but not how to generate it automatically on those worksheets.

I know very basic programming and able to read some VBA, but no more than that.

Any help will be appreciated

Thanks,

Udish12

 

[Herakles]

Can you please post your code as in the image using the 'Quick wrap selection as VBA code' option.

 

[Herakles]

Add these three lines under the line ActiveSheet.Paste.

If i > 1 Then
                Ws.Range("A5").Formula = "=SUM('" & MonthName(Month(MonthX)) & " " & i - 1 & "'" & "!A1:A4)"
            End If

 

[Udish12]

Can you please post your code as in the image using the 'Quick wrap selection as VBA code' option.

View attachment 85107

Can you please post your code as in the image using the 'Quick wrap selection as VBA code' option.

View attachment 85107

Sub Macro1()
Dim WS As Worksheet, WB As Workbook, TempWs As Worksheet, TempRange As Range
Dim MonthX As Date, Control As Variant, DaysInMonth As Byte, i As Byte, OldSheetCount As Byte

Set TempWs = ActiveSheet
Set TempRange = TempWs.Range("A1:P70") 'Range of template goes here
Control = InputBox("Enter month in the form of mm/yyyy.", "Month Entry", Month(Date) & "/" & Year(Date))
If IsDate(Control) Then
MonthX = CDate(Control)
DaysInMonth = Day(DateSerial(Year(MonthX), Month(MonthX) + 1, 0))
OldSheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = DaysInMonth
Set WB = Workbooks.Add
Application.SheetsInNewWorkbook = OldSheetCount
i = 1
For Each WS In WB.Sheets
WS.Name = MonthName(Month(MonthX)) & " " & i
TempWs.Activate
TempRange.Select
Selection.Copy
WS.Activate
WS.Cells(1, 1).Select
ActiveSheet.Paste
i = i + 1
Next
Else
MsgBox "Error while inputing start date."
End If
End Sub

 

[Udish12]

Hi Herakles,
The A= SUM(A1:A4) was only a sample.
What I really need is to be able to copy columns A1:A70 & columns B1:B7 from the previous day to the same columns on the "current date", also copy values from C64,D64 & G64 of the previous date to cells C2, D2 & G2 on the "current date".
Thanks a lot for your great help.
Udish12

 

[Herakles]

Do you actually mean copy these values or make reference to them?

 

[Udish12]

Do you actually mean copy these values or make reference to them?

Hi,
I need the values that I referred on my previous reply from "day1" on "day2" then from "day2" on "day3" and so on.
To be more clear, each worksheets represents a day of the month and the available stock of some materials on that date. On the current date (today) we register updates, new materials etc.,. The results of those updates need to be reflected on the next day.

To achieve that I need those worksheets to be able to update the values of columns A1:A70 & B1:B70 on day2 from the same columns on day1 and on day3 from day2 and so on, and also to update the values of cells C2, D2 & G24 on day2 from the cells C64, D64 & G64 from day1 and so on.
Remembering that the worksheets are created by the VBA macro.

I hope it's more clear.

Thanks

 

[Herakles]

Because it has to be dynamic, formulas have been added to refer to cells in the previous days worksheet.

Sub Macro1()
Dim WS As Worksheet
Dim WB As Workbook
Dim TempWs As Worksheet
Dim TempRange As Range
Dim MonthX As Date
Dim Control As Variant
Dim DaysInMonth As Byte
Dim i As Byte
Dim OldSheetCount As Byte
Dim strPath As String
Dim WsPrev As Worksheet

    strPath = ActiveWorkbook.Path & "\"
    
    Set TempWs = ActiveSheet
    
    Set TempRange = TempWs.Range("A1:P70") 'Range of template goes here
    
    ' Enter the month and year.
    Control = InputBox("Enter month in the form of mm/yyyy.", "Month Entry", Month(Date) & "/" & Year(Date))
    
    If IsDate(Control) Then
        
        MonthX = CDate(Control)
        
        ' Ascertain the number of days in the entered month.
        DaysInMonth = Day(DateSerial(Year(MonthX), Month(MonthX) + 1, 0))
                
        'Set the default number of workbook sheets in new workbooks.
        OldSheetCount = Application.SheetsInNewWorkbook
        
        ' Create a new workbook with the same number of sheets as in the template.
        Application.SheetsInNewWorkbook = DaysInMonth
        Set WB = Workbooks.Add
        WB.SaveAs strPath & MonthName(Month(MonthX)) & " " & Year(Control)
        Application.SheetsInNewWorkbook = OldSheetCount
        
        i = 1
    
        ' Loop through all worksheets, rename and copy data from template..
        For Each WS In WB.Sheets
            
            WS.Name = MonthName(Month(MonthX)) & " " & i
            
            TempRange.Copy WS.Range("A1")
                                  
            If i > 1 Then
            
                ' Apply formulas to get get values from previous days sheet.
               With WS
                    .Range("A1:B70").Formula = "='" & WsPrev.Name & "'!A1"
                    .Range("C2").Formula = "='" & WsPrev.Name & "'!C64"
                    .Range("D2").Formula = "='" & WsPrev.Name & "'!D64"
                    .Range("G24").Formula = "='" & WsPrev.Name & "'!G64"
                End With
               
                
            End If
            
            Set WsPrev = WS
            
             i = i + 1
             
        Next WS
        
        WB.Save
    
    Else
    
        MsgBox "Error while inputing start date.", vbCritical, "Warning!"
    
    End If

End Sub

 

[Udish12]

Works great. Thanks a lot for your amazing help!!!!!