How to "operate" with a cell in a previous worksheet?

[Michele317]

Hello,
I'm trying to find a way to operate only with the previous sheet.
I explain better what I mean with an example.

I have 3 sheets: A, B e C.
In sheet A I have a value in a cell, for example 1.
In sheet B I have 2.
In sheet C I have 3.

What I want to be able to do is to have a cell in sheet B that, for example, sum the value in the same sheet (sheet B) with the value in the previous sheet (sheet A). So, in sheet B i would have in a cell the value 1+2=3
In sheet C I want to do the same. So, in a cell in sheet C I would have 3+2=5

I have tried to play with the .Count() function but I think it's a little to vulnerable and easy to break. This because, if I create a sheet and then i delete it, the whole macro broke.
I post down what I have done and that has the problem I just explained:

Sub Calcolo_tot()

    Dim i As Integer
    i = Worksheets.Count - 1

    Sheets(Worksheets.Count).Range("J35").Value = Sheets(Worksheets.Count).Range("J34").Value + Worksheets(i).Range("J35")
 
End Sub

Thank you all in advance

 

[abdelfattah]

Hi,
I suspect you want show result in the same cell is already existed value!
based on what I understand above you can try this

Sub Calcolo_tot()
Dim sh1, sh2, sh3 As Worksheet
Set sh1 = Sheets("A")
Set sh2 = Sheets("B")
Set sh3 = Sheets("C")
sh2.Range("J35").Value = sh1.Range("J34").Value + sh2.Range("J34").Value
sh3.Range("J35").Value = sh2.Range("J34").Value + sh3.Range("J34").Value
End Sub

the values should be in J34 for sheets the result will be in J35.

 

[Michele317]

Hi,
I suspect you want show result in the same cell is already existed value!
based on what I understand above you can try this

Sub Calcolo_tot()
Dim sh1, sh2, sh3 As Worksheet
Set sh1 = Sheets("A")
Set sh2 = Sheets("B")
Set sh3 = Sheets("C")
sh2.Range("J35").Value = sh1.Range("J34").Value + sh2.Range("J34").Value
sh3.Range("J35").Value = sh2.Range("J34").Value + sh3.Range("J34").Value
End Sub

the values should be in J34 for sheets the result will be in J35.

Hi and thank you for the answer. The problem is that every month I make a new sheet, with a different name ("month year"). And so each month I would have to make a new variable and a new line. That's why i tried to use the counter.

 

[abdelfattah]

I suggest use XL2BB tool and show data before and after it until members help you.
good luck.