Finding the Total cell value across multiple Worksheets

[b9k9uk]

I have a WorkBook which produces weekly Time sheets for staff.

The first 3 sheets are the 'Admin' sheets; Start (produces week date and number copied across all sheets in VBA); Dutys (reads the Duty number and copies hours and time on duty) and Blank Time Sheets.

Each subsequent sheet is produced by copying Blank Time Sheets as new sheet (via VBA); renaming it to the Staff member name; Jones A; Smith A etc etc

The number of Sheets (Staff) varies as Staff leave/are recruited.

I need to find the Total Hours worked by all staff.

In this instance I need to reference cell H34 on each worksheet (excepting the first 3 sheets) and find the total (i.e. the cell value for H34 from each worksheet produces the Total Hours worked.)

I am currently using the formula ='Jones A'!H34 + 'Smith A'!H34 etc.

This is not very flexible as staff leave/are recruited as the Formula has to be edited each time staff leave/are recruited.

Is there a way to do this in vba?

Many thanks for reading this and hoping you can help.

 

[Aladin Akyurek]

Create two new sheets, name them First and Last. Then place all of the relevant sheets between First and Last. Now you can invoke:

=SUM(First:Last!H34)

 

[b9k9uk]

Doh! How simple it looks when you know the answer!

Many thanks Aladin; opens up all sorts of possiblities now.

 

[Aladin Akyurek]

Doh! How simple it looks when you know the answer!

Many thanks Aladin; opens up all sorts of possiblities now.

You are welcome. Thanks for the update.