Summarizing multiple sheets onto a coversheet

[Jeffrey Green]

I have a worksheet for each month. Each sheet has Plant Codes along the top (row 1), and model names down the side (column A). There is a number in the cells for a ship count. All worksheets have identical layout.

I need to summarize the 12 sheets onto a "coversheet" . . .

I might just do it the "poor man's way" and click in cell B2 on my coversheet and then add together all of the same cell location from all 12 worksheets, then copy that across and down . . . that will probably work, but is there an easier way?

Thanks

 

[MickG]

Try this, for Sheet:- Summary
If it works you will have to remove the Headers, if so let me Know and I'll add some code for that.

[COLOR=navy]Sub[/COLOR] MG08Sep55
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ws [COLOR=navy]As[/COLOR] Worksheet
[COLOR=navy]Dim[/COLOR] oRws [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] k
oRws = 0
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ws [COLOR=navy]In[/COLOR] Worksheets
        [COLOR=navy]If[/COLOR] Not Ws.Name = "Summary" [COLOR=navy]Then[/COLOR]
        .Add Ws.Name, Ws.Range("A1").CurrentRegion.Value
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ws
DoEvents
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] .keys
    Sheets("Summary").Range("A1").Offset(oRws).Resize(UBound(.Item(k), 1), UBound(.Item(k), 2)) = .Item(k)
    oRws = oRws + UBound(.Item(k), 1)
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] With
MsgBox "Run"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[vidplaylist]

Use this example to understand.

Sheet1 Cell A2 value 40
Sheet2 Cell A2 Value 30
Sheet3 Cell A2 Value 20
Sheet4 Cell A2 Value 10

On summary sheet i can consolidate by putting this formula
=SUM(Sheet1:Sheet4!A2)

Please provide feedback.

 

[MickG]

I think my enthusiasm has given you something you don't want. !!!

 

[Jeffrey Green]

Thanks, I will give it a shot . .

But what about the headers?? Should I leave them in??

 

[Jeffrey Green]

Re: Summarizing multiple sheets onto a coversheet, Round 2

I tried using this today, =SUM(Sheet2:Sheet25!C6)

In cell C6 of the first sheet in the workbook. I get a #Name error.

I have 26 sheets total, sheet 1 is the summary sheet.

Thoughts?

Thanks

 

[Jeffrey Green]

Never mind, we changed sheet names prior to doing formula