Summing data across excell sheets in a summary sheet using macro

satishsahoo · Sep 30, 2011

Hello,
I am stuck with the following. Any help on the same would be much appreciated.
'''''''''''''''''''''''''''''''''''''''
I want to add cell B1 in all the worksheets of a workbook and put it in cell B1 of the summary sheet. I want to do it for all the sheets in the worksheet staring with name Satish. I want to repeat the above activity for each cell in the range B1 to D5 in the sheet summary. The follwing code doesn't work. Please help me fix it up.
........................
Sub consolidate()

Dim j As Long, k As Long, tot As Double
Dim c As Range

For Each c In Worksheets("master").Range("B1:D5")

j = Worksheets.Count
tot = 0
For k = 1 To j
If Worksheets(k).Name <> "master" Then
If LCase(Left(Worksheets(k).Name, 5)) = "satish" Then

tot = tot + Worksheets(k).c.Cells(1).Value
End If
End If

Next k

Worksheets("master").c = tot

Next c
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Thanking you for your sugggestions in advance.

drsarao · Sep 30, 2011

Welcome to MrExcel.
Try this:

Code:

Sub consolidate()
Dim i As Integer, j As Integer, tot As Double
For i = 1 To 5 'rows 1 to 5
    For j = 2 To 4 'cols B to D
        tot = 0
        For Each ws In Worksheets
            If LCase(Left(ws.Name, 6)) = "satish" Then
                tot = tot + ws.Cells(i, j)
            End If
        Next ws
        Worksheets("master").Cells(i, j) = tot
    Next j
Next i
End Sub

The code above uses 2 FOR loops for cycling thru rows and then columns in range B1:D5.
The inner FOR loop cycles thru worksheets and adds "satish*" sheets - putting the final value in "master"

In your code:

tot = tot + Worksheets(k).c.Cells(1).Value
the "c" here is not valid,
here Cells(1) actually gives value in the cell A1!
LCase(Left(Worksheets(k).Name, 5)) = "satish"
comparison above will work only if you put 6 instead of 5.
Worksheets("master").c = tot is again not valid
just c=tot will do.

satishsahoo · Sep 30, 2011

Thansk a lot drdrao. that really helps.

satishsahoo · Sep 30, 2011

I was a bit wrong in stating the problem statement. i want to do sth like this.

 The sheets are in the following format.

Sheet 1,
<table class="MsoNormalTable" style="width:573.0pt;margin-left:4.9pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="0" cellpadding="0" cellspacing="0" width="764"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.75pt"> <td colspan="2" style="width:280.4pt;border-top: solid windowtext 1.0pt;border-left:solid windowtext 1.0pt;border-bottom:none; border-right:none;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="374">

[FONT=&quot]Particulars[/FONT]

</td> <td style="width:85.4pt;border:none; border-top:solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot]2011[/FONT]

</td> <td style="width:68.4pt;border:none;border-top: solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot]2012[/FONT]

</td> <td style="width:66.4pt;border:none;border-top: solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot]2013[/FONT]

</td> <td style="width:72.4pt;border:none;border-top: solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="97">

[FONT=&quot]2014[/FONT]

</td> </tr> <tr style="mso-yfti-irow:1;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]INCOME[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91"> [FONT=&quot] [/FONT]
</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] [/FONT]

</td> <td style="width:72.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="97">

[FONT=&quot] [/FONT]

</td> </tr> <tr style="mso-yfti-irow:2;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Units generated Mn KWh[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 8.3 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 8.3 [/FONT]

</td> <td style="width:72.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="97">

[FONT=&quot] 8.2 [/FONT]

</td> </tr> <tr style="mso-yfti-irow:3;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Auxiliary Consumption[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 0.0 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 0.0 [/FONT]

</td> <td style="width:72.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="97">

[FONT=&quot] 0.0 [/FONT]

</td> </tr> <tr style="mso-yfti-irow:4;mso-yfti-lastrow:yes;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Units available for sale[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 8.3 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 8.2 [/FONT]

</td> <td style="width:72.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="97">

[FONT=&quot] 8.2 [/FONT]

</td> </tr> </tbody></table>
Sheet 2
<table class="MsoNormalTable" style="width:500.6pt;margin-left:4.9pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="0" cellpadding="0" cellspacing="0" width="667"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.75pt"> <td colspan="2" style="width:280.4pt;border-top: solid windowtext 1.0pt;border-left:solid windowtext 1.0pt;border-bottom:none; border-right:none;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="374">

[FONT=&quot]Particulars[/FONT]

</td> <td style="width:85.4pt;border:none; border-top:solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot]2012[/FONT]

</td> <td style="width:68.4pt;border:none;border-top: solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot]2013[/FONT]

</td> <td style="width:66.4pt;border:none;border-top: solid windowtext 1.0pt;background:#3366FF;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot]2014[/FONT]

</td> </tr> <tr style="mso-yfti-irow:1;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]INCOME[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91"> [FONT=&quot] [/FONT]
</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] [/FONT]

</td> </tr> <tr style="mso-yfti-irow:2;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Units generated Mn KWh[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 16.6 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 16.6 [/FONT]

</td> </tr> <tr style="mso-yfti-irow:3;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Auxiliary Consumption[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 0.1 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 0.1 [/FONT]

</td> </tr> <tr style="mso-yfti-irow:4;mso-yfti-lastrow:yes;height:15.75pt"> <td style="width:273.95pt;border:none; border-left:solid windowtext 1.0pt;background:#FDE9D9;padding:0cm 5.4pt 0cm 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="365"> [FONT=&quot]Units available for sale[/FONT]
</td> <td style="width:6.45pt;background:#FDE9D9; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="9"> [FONT=&quot] [/FONT]
</td> <td style="width:85.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="114">

[FONT=&quot] [/FONT]

</td> <td style="width:68.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="91">

[FONT=&quot] 16.6 [/FONT]

</td> <td style="width:66.4pt;background:#C5D9F1; padding:0cm 5.4pt 0cm 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="89">

[FONT=&quot] 16.5 [/FONT]

</td> </tr> </tbody></table>
In the summary sheet i want to agreegate the 2012 data together and 2013 data together and so on. And there are 20 such sheets with 20 years of data in them. Meaning I should combine 2012 generation data together in all the sheets but the problem is the 2012 data would be in Column D in the 2012 sheet and in column E in 2011 sheet. So while summing I should be shifting one column to the left. i guess offset can be used to achieve the same but when I use offset there would be years when the offset would take the column location to less than the first column in cases.

Please help.Thanks

drsarao · Sep 30, 2011

Please explain the worksheet structure more.
I understand:

Excel Workbook

A

B

C

D

E

1

Particulars

2011

2012

2013

2014

2

INCOME

3

Units generated Mn KWh

0

4

Auxiliary Consumption

8.3

8.2

5

Units available for sale

8.3

8.2

Sheet1

Excel 2003

Is this depiction correct?
Do all the 20 sheets have similar structure? Meaning data in range B3:D5? (and not in B1:D5 as you had initially mentioned.)
Does the top row B1:D1 carry the year?
How is the "master" organized? Similarly? I presume master will have columns for 20 years. But is the basic structure same?

satishsahoo · Sep 30, 2011

Hello..
The basic structure is same for all the sheets including the master. but the problem is the satish2011 sheet would have generation data (i.e no of units generated, you must have understood I am trying to set up a financila model for a power generating unit) from 2011 onwards till next 20 years. satish2012 would have generation data from 2012 onwards till next 20 years. The data starts from cell B3 in all the satish* documents, but in satish2011 document the generation data of 2011 would be in cell B1. 2012 data would be in cell B2. But in satish2012 document the 2012 data would be in cell B1 as it would start with cell B1 again. Hope the below lay out would clarify further.

yes the year number is there on every column.

satish2011
B1 B2 B3
2011 2012 2013
15 17 18

satish2012
B1 B2 B3
2012 2013 2014
15 17 18

I want to add the 2011,2012,2013 numbers in the summary sheet.

summary

B1 B2 B3 B4
2011 2012 2013 2014
15 17+15 17+18

drsarao · Oct 3, 2011

If the structure is given in my post above, the following code should work:
(Please try on a copy first!)

Code:

Sub consolidate()
Dim i As Integer, j As Integer, tot As Double
Dim UGen(1 To 1, 1 To 20) As Double 'Unit generated
Dim Aux(1 To 1, 1 To 20) As Double 'Auxillary units
Dim UAv(1 To 1, 1 To 20) As Double 'Units available
For Each ws In Worksheets
    If LCase(Left(ws.Name, 6)) = "satish" Then
        yr = CInt(Right(ws.Name, 4)) 'get sheet name tail and covert to number (start year)
        For c = 2 To 4 'cycle columns
            UGen(1, yr - 2010 + c - 2) = UGen(1, yr - 2010 + c - 2) + ws.Cells(3, c)
            Aux(1, yr - 2010 + c - 2) = Aux(1, yr - 2010 + c - 2) + ws.Cells(4, c)
            UAv(1, yr - 2010 + c - 2) = UAv(1, yr - 2010 + c - 2) + ws.Cells(5, c)
        Next c
    End If
Next ws
With Sheets("master")
    .Range("B3:U3") = UGen
    .Range("B4:U4") = Aux
    .Range("B5:U5") = UAv
End With
End Sub

satishsahoo · Oct 4, 2011

Hello drsarao,
Its kind of you to help.
I wish there was a way to upload my file here. The sheet 1 ie satish2011 is exactly the way u have pasted in your post. But satish2012 would start from 2012 in B1 rather than 2011 in B1. satish2013 would start from 2013 in B1 and so on. And I want to add all the units generated in 2011 across the sheets in summary sheet. And do the same for 2012 data and so on.

The code you have pasted above adds for example all the C3s together and puts in the summary sheet. But the problem is while C3 of satish2011 would have units generated in 2012 C3 of satish2012 would have unitsgenerated in 2013. We don't want to add 2012 data to 2013 data we want to add all the 2012 data together.
I understand this might still be unclear to you. I wish I could do better by uploading or sth.
I appreciate your help and patience. Thanks

drsarao · Oct 4, 2011

Did you actually run the code and see?
If the structure of the sheets and naming is as mentioned, it should work as per your req. It takes into account the years by taking it from sheet name.

satishsahoo · Oct 4, 2011

Thanks drsarao. I changed some parameters based on the sheet lay out and your code worked. Thanks. The code is as follows.

.................................
Sub consolidatetrial()

Dim i As Integer, j As Integer, tot As Double
Dim UGen(1 To 1, 1 To 27) As Double 'Unit generated
Dim Aux(1 To 1, 1 To 27) As Double 'Auxillary units
Dim UAv(1 To 1, 1 To 27) As Double 'Units available
For Each ws In Worksheets
If LCase(Left(ws.Name, 5)) = "solar" Then
yr = CInt(Right(ws.Name, 4)) 'get sheet name tail and covert to number (start year)
For c = 4 To 28 'cycle columns
UGen(1, yr - 2010 + c - 4) = UGen(1, yr - 2010 + c - 4) + ws.Cells(88, c + 1)
Aux(1, yr - 2010 + c - 4) = Aux(1, yr - 2010 + c - 4) + ws.Cells(89, c + 1)
UAv(1, yr - 2010 + c - 4) = UAv(1, yr - 2010 + c - 4) + ws.Cells(90, c + 1)
Next c
End If
Next ws
With Sheets("ConsolidatedSolar")
.Range("E88:AC88") = UGen
.Range("E89:AC89") = Aux
.Range("E90:AC90") = UAv
End With
End Sub
....................................

Summing data across excell sheets in a summary sheet using macro

satishsahoo

New Member

Excel Facts

drsarao

Well-known Member

satishsahoo

New Member

satishsahoo

New Member

drsarao

Well-known Member

satishsahoo

New Member

drsarao

Well-known Member

satishsahoo

New Member

drsarao

Well-known Member

satishsahoo

New Member

Similar threads

Forum statistics

Share this page

Summing data across excell sheets in a summary sheet using macro

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock