SUM of data from multiple sheets in the mastersheet

[Ren]

hi
i have data in multiple sheets
i want the summary of data in a mastersheet
ex: sheet1 to sheet6

the code is like this

Range("B20") = "summary"
Range("C20").Formula = "=sum('sheet1:sheet6'!c30)"


this code gets the SUM of all data at C30 in sheet1 to sheet6


what i want: is there anyway i can change this so that
i don't have to mention the B20 or C30??

i just say get the "summary" of all the shipout materials
to mastersheet and should be able to get the summary irrespective of the
cell wherever it is.
because everyweek the data keeps adding and i have to change the code
everytime

thanks
Ren

 

[Per Jessen]

hi
i have data in multiple sheets
i want the summary of data in a mastersheet
ex: sheet1 to sheet6

the code is like this

Range("B20") = "summary"
Range("C20").Formula = "=sum('sheet1:sheet6'!c30)"


this code gets the SUM of all data at C30 in sheet1 to sheet6


what i want: is there anyway i can change this so that
i don't have to mention the B20 or C30??

i just say get the "summary" of all the shipout materials
to mastersheet and should be able to get the summary irrespective of the
cell wherever it is.
because everyweek the data keeps adding and i have to change the code
everytime

thanks
Ren

Hi Ren

Name the cells, and then refer to the cells. Have a look at this:

Option Explicit
Dim sh As Single
Dim MySum As Long

Sub assignName()
'Name cells
sheets("MasterSheet").range("A1").Name = summary
For sh = 1 To 6
Sheets(sh).Range("A1").Name = "summary" & sh ' Change to suit the cell
Next
End Sub

Sub SumCells()
For sh = 1 To 6
MySum = MySum + Sheets(sh).Range("summary" & sh).Value
Next
Sheets("MasterSheet").Range("summary") = MySum
End Sub

Regards,

Per

 

[Ren]

Hi Per,

thanks for the reply

i have around 20 sheets, and each sheet name is different.
problem is,if i have to sum up anything, i have to maintain the same range
in all the sheets.(the data keeps adding everyweek)

 

[Ron de Bruin]

See this page Ren
http://www.rondebruin.nl/linksum.htm