SUM of data from multiple sheets in the mastersheet

R

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
 
P

Per Jessen

Ren said:
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
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top