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

 

[StephenCrump]

Did you use the code exactly as provided in Post #5?

If I start with:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative	3											Day total	3
1

Cell Formulas
Range	Formula
E7	E7	=Q7

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative												Day total	5
2

and then run the macro, Sheet 2 becomes (as you require):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative	8											Day total	5
2

Cell Formulas
Range	Formula
E7	E7	='1'!E7+Q7

 

[akrameldaly]

It works for me thank you so much.

Did you use the code exactly as provided in Post #5?

If I start with:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative	3											Day total	3
1

Cell Formulas
Range	Formula
E7	E7	=Q7

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative												Day total	5
2

and then run the macro, Sheet 2 becomes (as you require):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7				Cumulative	8											Day total	5
2

Cell Formulas
Range	Formula
E7	E7	='1'!E7+Q7

 

[StephenCrump]

It works for me thank you so much.

Great! Thanks for posting back.

 

[akrameldaly]

Now im repeating the formula for another cells as follows

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"
Worksheets(CStr(i)).Range("E8").Formula = "=" & i - 1 & "!E8+Q8"
Worksheets(CStr(i)).Range("E9").Formula = "=" & i - 1 & "!E9+Q9"
Worksheets(CStr(i)).Range("E10").Formula = "=" & i - 1 & "!E10+Q10"
Worksheets(CStr(i)).Range("E11").Formula = "=" & i - 1 & "!E11+Q11"

For i = 2 To 31
Worksheets(CStr(i)).Range("D7").Formula = "=" & i - 1 & "!D7+D7"
Worksheets(CStr(i)).Range("D8").Formula = "=" & i - 1 & "!D8+D8"
Worksheets(CStr(i)).Range("D9").Formula = "=" & i - 1 & "!D9+D9"
Worksheets(CStr(i)).Range("D10").Formula = "=" & i - 1 & "!D10+D10"
Worksheets(CStr(i)).Range("D11").Formula = "=" & i - 1 & "!D11+D11"
For i = 2 To 31
Worksheets(CStr(i)).Range("F7").Formula = "=" & i - 1 & "!F7+F7"
Worksheets(CStr(i)).Range("F8").Formula = "=" & i - 1 & "!F8+F8"
Worksheets(CStr(i)).Range("F9").Formula = "=" & i - 1 & "!F9+F9"
Worksheets(CStr(i)).Range("F10").Formula = "=" & i - 1 & "!F10+F10"
Worksheets(CStr(i)).Range("F11").Formula = "=" & i - 1 & "!F11+F11"
For i = 2 To 31
Worksheets(CStr(i)).Range("G7").Formula = "=" & i - 1 & "!G7+G7"
Worksheets(CStr(i)).Range("G8").Formula = "=" & i - 1 & "!G8+G8"
Worksheets(CStr(i)).Range("G9").Formula = "=" & i - 1 & "!G9+G9"
Worksheets(CStr(i)).Range("G10").Formula = "=" & i - 1 & "!G10+G10"
Worksheets(CStr(i)).Range("G11").Formula = "=" & i - 1 & "!G11+G11"


Next i

On Error GoTo 0

End Sub


It Works for the E column only hw i can make it works for columns D,F & G

 

[StephenCrump]

Your formula for columns D, F and G refers to itself, so you'll get circular references.

What's in '2'!D7? If it's SomeFormula, then you can use:

Worksheets(CStr(i)).Range("D7").Formula = "=" & i - 1 & "!D7+SomeFormula"

But if it's a value, you'll need to use a cell other than D7 for your cumulative monthly sum.

By the way:

'You can replace ...
Worksheets(CStr(i)).Range("E7").Formula = "=" & i - 1 & "!E7+Q7"
Worksheets(CStr(i)).Range("E8").Formula = "=" & i - 1 & "!E8+Q8"
Worksheets(CStr(i)).Range("E9").Formula = "=" & i - 1 & "!E9+Q9"
Worksheets(CStr(i)).Range("E10").Formula = "=" & i - 1 & "!E10+Q10"
Worksheets(CStr(i)).Range("E11").Formula = "=" & i - 1 & "!E11+Q11"

'with ...
Worksheets(CStr(i)).Range("E7:E11").Formula = "=" & i - 1 & "!E7+Q7"

 

[akrameldaly]

Thank u so much for your help @StephenCrump

one last thing i need to make the date change in cell i2 for example

sheet '1' 1/1/2024 , sheet '2' 2/1/2024, sheet '3' 3/1/2024 and so on

 

[StephenCrump]

Dim StartDate As Long, i As Long

StartDate = DateSerial(2024, 1, 1)

For i = 1 To 31
    With Worksheets(CStr(i)).Range("I2")
        .Value = StartDate + i - 1
        .NumberFormat = "D/M/YYYY"
    End With
Next i