Solved: Create a Summary Totals Sheet

[NewDaddy]

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]

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]

Um. . .NeverMind

[NewDaddy]

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]

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.

[VBA]
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

[/VBA]

Hope that gets you going.


Tony

[NewDaddy]

Thank you.

That gets me started.

I will let you know how I get on.

Cheers
Jay

[NewDaddy]

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