Solved: Code Help

[drums4monty]

Hi

I need to add up all figures in H44 and then H45, then add these 2 figures together across 62 worksheets from 2 to 63. I have written this code but it returns a 0 figure. I thought I might be able to work this one out but I carn't :( Any help?



Sub NameBadgeTotal()
For i = 2 To 63
With Worksheets(i)
a = H44
b = H45
c = a + b
End With
Next i
Sheets("Summary").Select
Range("D49").Select
ActiveCell = c

End Sub



Regards

Alan

[tstav]

Wrong post

[tstav]

Sub NameBadgeTotal()
For I = 2 To 63
With Worksheets(I)
a = .Range("H44").Value
b = .Range("H45").Value
c = c + a + b
End With
Next I
Sheets("Summary").Select
Range("D49").Select
ActiveCell = c
End Sub

[drums4monty]

Thanks tstav thats great. Now one other thing, is there a way to get it to automatically update D49 if any of the worksheet (H44 or H45) change?

[Bob Phillips]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const WS_RANGE As String = "H44:H45" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then
With Target
Sh.Range("D49").Value = Sh.Range("H44").Value + Sh.Range("H45").Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[drums4monty]

Hi Bob

Did that but it does not work, probably because I don't know what I a doing. I copied and pasted it as you said.

Alan

[Bob Phillips]

Sounds as though you didn't implement it correctly Alan.

One thing to note is that it only updates the sheet changed, so any change to H44, H45 updates D49 on that sheet, i.e. it doesn't loop them all like the original code, that s just wasteful when the values are not changing.

[drums4monty]

I see, many thanks Bob, I will keep trying. I was quite pleased that my original code was not too far of the mark, I'm getting there . . . slowly.

Alan

[tstav]

This does the loop through all 62 worksheets (except for the "Summary")

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
Dim a, b, c
If Sh.Name <> "Summary" And _
Not Intersect(Target, Sh.Range("H44:H45")) Is Nothing Then
For i = 2 To Worksheets.Count
With Worksheets(i)
a = .Range("H44").Value
b = .Range("H45").Value
c = c + a + b
End With
Next i
Worksheets("Summary").Range("D49").Value = c
End If
End Sub

[Krishna Kumar]

Move the result sheet before the first sheet. Insert one sheet each before and after your data sheets and name them "First" and "Last" (these two sheets must be empty). Now in D49 on your summary sheet

=SUM(First:Last!H44:H45)

HTH

[tstav]

Super solution, Krishna Kumar!

No need though for the extra sheets.
=SUM(SheetX:SheetY!H44:H45).

[Bob Phillips]

The idea of the extra sheets is so that you can insert new ones without worrying about updating the formulae. Even better if you hide them.

[drums4monty]

Thanks all I've learnt a lot today. My first attempt was at trying to programme myself and I was pleased to how far I got, and with the help of tstav I learnt what I needed to know. The solution by Krishna does the job brilliantly and I would never of thought of that way. How does it work though?

Regards

Alan

[Bob Phillips]

Same as SUM(A1:X100) works, Excel knows what is between A1:X100 and grabs the data from each. Nothing clever at all, standard 3D summing.

[drums4monty]

Thanks Bob