Summary page for 12 worksheets

[Guest]

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

 

[Dave Peterson]

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

 

[MGC]

Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new sheet
via the duplication of the previous sheet but this may have to be deleted due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a sample
of the workbook to your personal address if needed. Thank you for your time!

 

[Dave Peterson]

I would use a macro--so that won't work for you.

You may want to post in the .worksheet.functions newsgroup. Maybe there's some
giant formulas you could use (way beyond me!).

 

[Roger Govier]

Hi

Pivot tables would work, there are just more fields to select than you led
me to believe in your previous posting.

Anyway, another alternative might be as follows.
Ensure that Order number is the first column on all of your sheets.
If they are not, then select first sheet>hold down Shift>Select last sheet
and this will Group the sheets.
Select the column with Order Number>Cut>Select column A>Insert Cut cells
Select any individual sheet tab to Ungroup the selection

On a blank sheet that is to be your Summary sheet, choose Data>Consolidate
Leave the first dialogue box as Sum.
Click on your first sheet, select all the columns that you need>click
Add>move to second sheet, the range will later to reflect the new sheet
name>Click Add>repeat until all sheets required have been selected.
Click Use Label in Top row and Left Column>OK

This will give you Totals of Hours for each Order number
Whenever you need to refresh, just go to this new Summary sheet and Choose
Data>Consolidate>OK as all the ranges will have been remembered.

 

[MGC]

Dave/Roger:

Sorry for the lateness of my reply. If I wanted to run this with my current
macro, could I? and what would I need to do to be able to do this? The
following is the macro currently in use:

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy at the 'end' of the workbook

Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)
'remains on the new sheet
End Sub

How do you convince your company that the macro doesn't contain a
virus...that's the reason I may need to stop using it. Thanks for all the
help you guys!!!

 

[Dave Peterson]

Most of the original thread has aged off for me. But there's nothing in your
code that precludes running that on any "normal" macro (that I can see, anyway).

But I'd use:
ActiveSheet.Copy after:=Sheets(sheets.Count)
instead of:
Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)

Dave/Roger:

Sorry for the lateness of my reply. If I wanted to run this with my current
macro, could I? and what would I need to do to be able to do this? The
following is the macro currently in use:

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy at the 'end' of the workbook

Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)
'remains on the new sheet
End Sub

How do you convince your company that the macro doesn't contain a
virus...that's the reason I may need to stop using it. Thanks for all the
help you guys!!!

 

[MGC]

Dave:

This is my original post (and the one I am looking for a macro for and how
to set up). Please let me know if you can help.

Dave:

 

[Dave Peterson]

Maybe...

This deletes the worksheet named summary and recreates it.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim SumWks As Worksheet
Dim DestCell As Range
Dim myClasses As Variant
Dim HowManyClasses As Long
Dim myHourTypes As Variant
Dim HowManyHourTypes As Long
Dim HowManyRows As Long
Dim iCtr As Long
Dim myFormula As String

'remove the Summary worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True
On Error GoTo 0

myClasses = Array("GF", "F", "JW", "AP")
HowManyClasses = UBound(myClasses) - LBound(myClasses) + 1

myHourTypes = Array("ST", "OT", "DT")
HowManyHourTypes = UBound(myHourTypes) - LBound(myHourTypes) + 1

HowManyRows = HowManyClasses * HowManyHourTypes

Set SumWks = Worksheets.Add
With SumWks
.Name = "Summary"
.Range("A1").Resize(1, 5).Value _
= Array("WksName", "Date", "Key", "Hr Type", "hours")
Set DestCell = .Range("a2")
End With

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case Is = SumWks.Name
'do nothing
Case Else
'put the worksheet name in column A
DestCell.Resize(HowManyRows, 1).Value = "'" & wks.Name

'put the date in column B
With DestCell.Offset(0, 1).Resize(HowManyRows, 1)
.Value = wks.Range("b6").Value
.NumberFormat = "mm/dd/yyyy"
End With

'put the formula to determine hours in column E
myFormula _
= "=SUMPRODUCT(--('" & wks.Name & "'!R9C3:R44C3=RC3)," _
& "--('" & wks.Name & "'!R9C5:R44C5=RC4)," _
& "'" & wks.Name & "'!R9C6:R44C6)"
myFormula = Replace(myFormula, "@", Chr(34))

DestCell.Offset(0, 4).Resize(HowManyRows, 1).FormulaR1C1 _
= myFormula

'put the key and hour types options in C
For iCtr = LBound(myHourTypes) To UBound(myHourTypes)
DestCell.Offset(0, 2).Resize(HowManyClasses, 1).Value _
= Application.Transpose(myClasses)
DestCell.Offset(0, 3).Resize(HowManyClasses, 1).Value _
= Application.Transpose(myHourTypes(iCtr))
Set DestCell = DestCell.Offset(HowManyClasses, 0)
Next iCtr

End Select
Next wks

End Sub


I like to know where the data comes from. I put the worksheet name in column
A. The date in column B. The class type in column C. The hour type in D and
the total hours in column E.

In R1C1 reference style, this: R9C3:R44C3
is row 9, column 3 through row 44, column 3
(C9:C44 in A1 reference style)

And RC3 means the same row column 3 (C### in A1 reference style)

After the data is laid out like this, you could use a pivottable to see nice
summaries.

Dave:

This is my original post (and the one I am looking for a macro for and how
to set up). Please let me know if you can help.

Dave:

 

[Dave Peterson]

You could delete this line, too:
myFormula = Replace(myFormula, "@", Chr(34))

And some notes about the =sumproduct() formula...

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[MGC]

Thanks, Dave! I will definitely try this and see what happens. Can I add
this to my already existing macro or does this have to be a new one?

You could delete this line, too:
myFormula = Replace(myFormula, "@", Chr(34))

And some notes about the =sumproduct() formula...

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

I'd use a new macro until you got it working.

But I still don't know what your existing macro does.

You can give this a nice name and then just call it with a line like:
Call SummarizeMySheets

Thanks, Dave! I will definitely try this and see what happens. Can I add
this to my already existing macro or does this have to be a new one?