PDA

View Full Version : Solved: Create a Summary Totals Sheet



NewDaddy
11-07-2006, 07:43 AM
Hi All

Once again I am in need of your help.

I could probably put this together (eventually) but I don?t have the time hence my coming to you.

I need to create a summary/totals sheet showing the ?Grand Total? of budget changes for all worksheets in a workbook. The sheets where based on a pivot table and subsequently have been 'fanned out' and made inactive/pasted valued.

What I would like returned onto the summary/total sheet is, as an example, as follows;

Division Amount
Sheet1 (name) 0.00
Sheet2 (name) 0.00
Etc
Etc

"Grand Total" will be found in Column A
The total change (amounts column) is in Column E

Column A - Reason for Movement
Column B - Revenue Budget Code Used
Column C - Orginal Budget
Column D - Revised/New Budget
Column E - Difference between Original & Revised Budgets (ie change)

I think I have made enough sence, sorry for the rush but yet something else to deal with.

Cheers
Jay

acw
11-07-2006, 04:34 PM
Jay

1) Do you want a formula result, or a macro
2) Is the grand total in a consistent place, or is it likely to be variable
3) What is the name of the summary sheet and the data sheets.


Tony

SamT
11-07-2006, 09:03 PM
Um. . .NeverMind

NewDaddy
11-08-2006, 07:13 AM
Jay

1) Do you want a formula result, or a macro
2) Is the grand total in a consistent place, or is it likely to be variable
3) What is the name of the summary sheet and the data sheets.


Tony

Hi Tony

I have put together using formula after thinking I could do it that way.

It would be good to have a routine that would do it as it would save some time.

Both amounts (total) and sheet names are variable.

Cheers
Jay

ps sorry for not taking so long, I lost internet access yesterday and have only just got it back.

acw
11-08-2006, 03:17 PM
Jay

The following will look at all the sheets in the workbook. The output sheet is deemed to be Summary, and will not be processed.


Sub aaa()
Dim ws As Worksheet
Dim OutPl
For Each ws In Sheets
If ws.Name <> "Summary" Then
Set findit = ws.Range("A:A").Find(what:="Grand Total", lookat:=xlWhole)
If Not findit Is Nothing Then
Set OutPl = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
OutPl.Value = "Grand Total"
OutPl.Offset(0, 1).Value = findit.Offset(0, 4).Value
End If
End If
Next ws

End Sub



Hope that gets you going.


Tony

NewDaddy
11-09-2006, 06:11 AM
Thank you.

That gets me started.

I will let you know how I get on.

Cheers
Jay

NewDaddy
11-10-2006, 01:09 AM
Dear Tony

Thanks for your solution.
As it is it works great. I have personalised it a bit and it now does exactly what I need.

As ever, very grateful for you help. You have saved me some time and I am now going to go away and experiment.

Have a great weekend.
Jay