Macro for Sum of a value in a specific cell to value of a cell in another sheet

[akrameldaly]

I have a workbook for daily report where each sheet in the workbook represents daily production records, i have used a formula on E7 = '1'!E7+'2'!$Q7, where '1' is the first day in the month and '2' is the 2nd day in the month and repeat this for each day till day the last day of the month by modifying the day number in the formula, now i need help to use a macro to solve this problem and run macro instead of repeating this task each day

 

[arthurbr]

Hi so you need to sum different cells on different sheets ? ( E7 and Q7 in your example)?

 

[akrameldaly]

Hi so you need to sum different cells on different sheets ? ( E7 and Q7 in your example)?

yes exactly and E7 is from the preceding day

 

[akrameldaly]

does anyone can help.

 

[StephenCrump]

i have used a formula on E7 = '1'!E7+'2'!$Q7 ....

Welcome to the Forum!

E7 on sheet '1', and Q7 on sheet '2'. Which cell on sheet '3', sheet '4' etc? And how will the macro know this?

It would be much simpler if you could put your results in the same cell on each sheet. If your sheets '1' to '31' are in consecutive order, then you can simply use, for example:

=SUM('1:31'!A1)

 

[akrameldaly]

Welcome to the Forum!

E7 on sheet '1', and Q7 on sheet '2'. Which cell on sheet '3', sheet '4' etc? And how will the macro know this?

It would be much simpler if you could put your results in the same cell on each sheet. If your sheets '1' to '31' are in consecutive order, then you can simply use, for example:

=SUM('1:31'!A1)

Each day sum of the previous day like '2'E7 = '1'!E7+'2'!$Q7, '3' E7 = '2'!E7+'3'!$Q7, '4' E7 = '3'!E7+'4'!$Q7

E7 has the cumulative value.
Q7 has the day to day value

 

[StephenCrump]

And do you want the macro to populate these formulae? If so, try:

Sub Test()

    Dim i As Long
    On Error Resume Next
    
    For i = 2 To 31
        Worksheets(CStr(i)).Range("E7").Formula = "=" & i - 1 & "!E7+Q7"
    Next i
    
    On Error GoTo 0

End Sub

 

[akrameldaly]

thank you so much i will try it

 

[akrameldaly]

it is not working!

 

[akrameldaly]

The result of !E7+Q7 is the same value as both cells from the same sheet it has to be from two different sheets E7 from the preceding sheet (i-1) andQ7 from the same sheet (i).