Macro To Give Totals

J

JAMhome

New Member
Joined
Apr 28, 2011
Messages
41
I have a summary sheet that will expand row wise. When the macro executes the data shows up in B2=Cost Summary. I want the date column to give me a count, the hourly rate, hours worked, shift total a sum. I want it to appear at the bottom.

JAM
This is what the summary sheet looks like:
<TABLE style="WIDTH: 315pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=420><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 72pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl64 height=20 width=96>Cost Summary</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 51pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" width=68></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 48pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 48pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 48pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 48pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0"></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0"></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0"></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0"></TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65 height=20>Worksheets</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65>Date</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65>Name/Location</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65>Hourly Rate</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65>Hours Worked</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65>Shift Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl66 height=20>TS1</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl68 align=right>5/14/2011</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0">DEPUTY DOG</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl67 align=right>$79.00 </TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" align=right>7</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl67 align=right>$260.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl66 height=20>TS2</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl68 align=right>5/30/2011</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0">RICHIE RICH</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl67 align=right>$79.00 </TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl67 align=right>$158.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl66 height=20>ChkptTS1</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl68 align=right>5/30/2011</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0; mso-ignore: colspan" colSpan=2>UHAUL CAMPUS</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl67 align=right>$268.00 </TD></TR></TBODY></TABLE>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi Jam,

I've got your new thread

I'll get back to you with some more questions in a while.

HTML: 
Hi Colin
 
I did start a new thread. That worked great. I was hoping to alter the code to fit my other sheets. For example, if the data appears in B1-G?, how would I alter the code? For example, if the data appears in B2-AA?, how would I alter the code? I was hoping for a universal macro two work in any sheet--if there is such a thing!! Anyway you are terrific--can you deal with me a bit longer and help me with the other sheets?
 
JAM
 
Upvote 0
Hi Jam,

If you want a code that can be used on a number of different layouts, there have to be some rules to enable the code to recognise what is what.

For example,

Can we always say that the header in coloumn A will be "Worksheets" ?

In the Column Headers for monetary values that require a total always have a "$" in the Header i.e. "Hourly Rate $", "Shift Total $".?


Can we always say that the headers for other non-monetary values that require a total always have "%" in the header, i.e. " Hours Worked %".


For Columns where just a count is required, is it possible that the header always includes a "#", i.e. "Date #" ?


If we can set some rules like this, we can put together a code that will deal with any layout.
 
Upvote 0
Hi Colin

Here is code where I would like to put the sums code. It is identical to the summary sheet but when I execute it the headings appear in Row 1, Cell B. This might be easier to alter. The other two tables are identical but I will get back to you on them.

JAM

Sub FilterSummaryCosts()
'Extracts date rows from Summary sheet and places them on new sheet
Dim iRow As Long
Dim dCol As Variant 'Date column specifier
Dim Drng As Range 'Range containing filter dates
Dim dRow As Integer 'row within FilterDates range
Dim ShNum As Integer
Dim ShName As String
Dim ShRow As Long 'row on destination sheet

Set Drng = Range("FilterDates")

dCol = InputBox("Enter Summary sheet column containing dates to be filtered" & vbLf & _
"Can be either number of letters (e.g., ""B"" or ""2"", without quotes)", _
"Identify column", "A")

If IsNumeric(dCol) Then dCol = CInt(dCol)

'create new sheet
ShNum = 1
ShName = "Holiday Costs(1)"
Do While SheetExists(ShName)
ShNum = ShNum + 1
ShName = "Holiday Costs(" & ShNum & ")"
Loop
Worksheets.Add After:=Worksheets("Cover")
ActiveSheet.Name = ShName
ShRow = 2 'Start with row 2 to bypass header row

With Worksheets("Summary")
'Copy header row of Summary sheet to new (destination) sheet
.Rows(4).Copy Destination:=Rows(1)
For iRow = .Cells(65536, dCol).End(xlUp).Row To 2 Step -1

For dRow = 1 To Drng.Rows.count
If Drng(dRow, 1) = .Cells(iRow, dCol) And Not IsEmpty(Drng(dRow, 1)) Then
' extraction date found
.Rows(iRow).Copy Destination:=Rows(ShRow)
'.Rows(iRow).Delete
ShRow = ShRow + 1
Exit For
End If
Next dRow

Next iRow
End With

'sort Extracted data by date
Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Sort Cells(1, dCol), xlAscending, Header:=xlYes

End Sub
Function SheetExists(ShName As String) As Boolean
On Error GoTo NoSuchSheet
Set sh = Sheets(ShName)
SheetExists = True
Exit Function
NoSuchSheet:
SheetExists = False
End Function
 
Upvote 0
Jam,

Before I can do anything with the code you set over, can you just confirm the questions from my previous post.

I'm out and about tomorrow so I wont be back to you until later.
 
Upvote 0
Hi Colin

The universal code would only apply to the two sheets and the format would not change--if I understand correctly. Here is the other sheet but it is not complete with all the columns--general numbers and a count for dates. Did I answer your question completely?

JAM
Out and about tomorrow too--no rush on this--one of the last things I can think of before I give it to users. Again you are terrific.

Here is the other sheet layout:
<TABLE style="WIDTH: 562pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=750><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=99>Summary Stats</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>Worksheets</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Total A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Total C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>C Warn</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Total D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>D Warn</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Felony Arr.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Recov'd Stolen Vec.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Fugitives App.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Susp. Lic</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>TS1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>12/15/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>TS2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>12/30/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>ChkptTS1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>11/22/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD></TR></TBODY></TABLE>
 
Upvote 0
Hi Jam,

I've put togethere a code that will work with any number of columns / rows, and a Header Row starting on any row, provided, that when the sheets are created / formated, the following rules apply.

"Worksheets" must be the Header in Column A.

Any column that requires a Count, There must be a "#" at he end of the Header Text i.e. "Date #"

Any column that requires a numerical Sum, There must be a "%" at he end of the Header Text i.e. "Total A%"

Any column that requires a Currancy Sum, There must be a "$" at he end of the Header Text i.e. "Shift Total $"

This is the code:

Code: 
Sub Totals()
'First get the last used row
x:
R = Range("A" & Rows.Count).End(xlUp).Row
'Check to see if it have previously been totaled, if so delete it and start again
If Cells(R, 1) = "Totals" Then Rows(R & ":" & R).EntireRow.Delete: GoTo x
'set the Totals row 2 rows further down
TR = R + 2
' the first Header in Column A will always be "Worksheets" but could start on any row from 1 down
ro = 1
Do While Cells(ro, 1) <> "Worksheets"
    ro = ro + 1
Loop
'The number of column Headers can vary, so find the last used column
intCol = ActiveSheet.UsedRange.Columns.Count
'Starting from Headers 2
For a = 2 To intCol
'Set w as the Column Alpha character(s)
    If a < 27 Then
        w = Chr$(a + 64)
    Else
        z = Int(a / 26)
        a = a - (z * 26)
        w = Chr$(z + 64) + Chr$(a + 64)
    End If
    'Set w as the Alpha / Numeric range first row below header to the last used row
    zz = w & (ro + 1) & ":" & w & R
    'Set the Select Case variable to "" and get the Header text
    x = "": z = Cells(ro, a).Text
    'Get the last character from the Header Text, this Determines if it is to be a
    '"Count" / "Sum" / Number / Currency
    z = Mid$(z, Len(z))
    'If there is one of the appropriate characters reset the Select Case variable x
    'If not leave x as ""
    If z <> "" Then x = z
    'Based on the Select Case variable, format the Total cell & load the appropriate formula
    Select Case x
        Case "$"
            Cells(TR, a).NumberFormat = "[$$-409]#,##0.00;[Red][$$-409]#,##0.00"
            Cells(TR, a).Formula = "=Sum(" & zz & ")"
        Case "%"
            Cells(TR, a).NumberFormat = "#,##0;[Red]#,##0"
            Cells(TR, a).Formula = "=Sum(" & zz & ")"
        Case "#"
            Cells(TR, a).NumberFormat = "#,##0;[Red]#,##0"
            Cells(TR, a).Formula = "=Count(" & zz & ")"
    End Select
    Cells(TR, 1) = "Totals"
Next a
End Sub

I've put together an example WB called "Test Summary Totals", which you can download from:

http://www.box.net/shared/fjs417b17q

Ctrl+q activates the code.
 
Upvote 0
Hi Colin

The worksheets "Cost Summary" and "Summary Stats" are generated with a macro. The "Worksheet" column name ends up in Column B.

Two other worksheets are generated from "Cost Summary" and "Summary Stats" that extract certain dates and produces the worksheets. Once again the "Worksheet" column name ends up in Column B.

Is there a way to change the total macro so "Worksheet" can be in Column B?

In a previous thread is the code for "Sub FilterSummaryCost" and I have no idea how to alter the code for the other criteria, such as, Date #, Total A%, and so on.

Sorry I am so needy.

JAM
 
Upvote 0
Hi Jam,

First, if "Worksheet" is going to be in Column B, then change the code I sent:

Code: 
Do While Cells(ro, 1) <> "Worksheets"

To:

Code: 
Do While Cells(ro, 2) <> "Worksheets"

In the "Sub FilterSummaryCost" code you posted, While I don't totally understand what is happening, it is clear that you are adding a new Sheet

Code: 
'create new sheet
ShNum = 1
ShName = "Holiday Costs(1)"
Do While SheetExists(ShName)
ShNum = ShNum + 1
ShName = "Holiday Costs(" & ShNum & ")"
Loop
Worksheets.Add After:=Worksheets("Cover")
ActiveSheet.Name = ShName

And you are copying the Header row from the "Summary" sheet :

Code: 
With Worksheets("Summary")
'Copy header row of Summary sheet to new (destination) sheet
.Rows(4).Copy Destination:=Rows(1)

So the headers have already been created somewhere else.

The "Date #", "Total A%" text needs to be changed wherever they are first created, and I don't know where that is.
 
Upvote 0
Hi Colin

After a night's sleep I realized I need to change the following code so worksheets ends up in column A, which is want I been wanting to do from the start.
Sorry to confuse. Thank you for putting up with me. JAM

Sub SummarizeInNewSheet()
Dim ws As Worksheet
Dim wsANm As String
Dim wsA As Worksheet
Dim r As Integer
Dim MyTot1 As Variant
Dim MyTot2 As Variant
Dim MyTot3 As Variant
Dim MyTot4 As Variant
Dim MyTot5 As Variant
Dim MyTot6 As Variant
Dim MyTot7 As Variant
Dim MyTot8 As Variant
Dim MyTot9 As Variant
Dim MyTot10 As Variant
'
' Cell that has the totals on each sheet
Set MyTot1 = Range("C7")
Set MyTot2 = Range("E13")
Set MyTot3 = Range("E14")
Set MyTot4 = Range("G14")
Set MyTot5 = Range("E15")
Set MyTot6 = Range("G15")
Set MyTot7 = Range("E16")
Set MyTot8 = Range("E17")
Set MyTot9 = Range("E18")
Set MyTot10 = Range("E19")
'
Set wsA = Worksheets.Add(Before:=Worksheets(1))
MyTot1 = MyTot1.Address
MyTot2 = MyTot2.Address
MyTot3 = MyTot3.Address
MyTot4 = MyTot4.Address
MyTot5 = MyTot5.Address
MyTot6 = MyTot6.Address
MyTot7 = MyTot7.Address
MyTot8 = MyTot8.Address
MyTot9 = MyTot9.Address
MyTot10 = MyTot10.Address
wsANm = wsA.Name
On Error Resume Next
wsA.Name = "Stats"
NoName: If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("Stats").Delete
Application.DisplayAlerts = True
wsA.Name = "Stats"
If wsA.Name = wsANm Then GoTo NoName
On Error GoTo 0
'
r = wsA.Range("B65536").End(xlUp).Row + 1
wsA.Cells(r, 2).Value = "Summary Stats"
wsA.Cells(r, 2).Font.Bold = True
wsA.Cells(r + 2, 2).Value = "Worksheets"
wsA.Cells(r + 2, 2).Font.Italic = True
wsA.Cells(r + 2, 3).Value = "Date #"
wsA.Cells(r + 2, 3).Font.Italic = True
wsA.Cells(r + 2, 4).Value = "Total A%"
wsA.Cells(r + 2, 4).Font.Italic = True
wsA.Cells(r + 2, 5).Value = "Total C%"
wsA.Cells(r + 2, 5).Font.Italic = True
wsA.Cells(r + 2, 6).Value = "C Warn%"
wsA.Cells(r + 2, 6).Font.Italic = True
wsA.Cells(r + 2, 7).Value = "Total D%"
wsA.Cells(r + 2, 7).Font.Italic = True
wsA.Cells(r + 2, 8).Value = "D Warn%"
wsA.Cells(r + 2, 8).Font.Italic = True
wsA.Cells(r + 2, 9).Value = "Felony Arr.%"
wsA.Cells(r + 2, 9).Font.Italic = True
wsA.Cells(r + 2, 10).Value = "Recov'd Stolen Vec.%"
wsA.Cells(r + 2, 10).Font.Italic = True
wsA.Cells(r + 2, 11).Value = "Fugitives App.%"
wsA.Cells(r + 2, 11).Font.Italic = True
wsA.Cells(r + 2, 12).Value = "Susp. Lic%"
wsA.Cells(r + 2, 12).Font.Italic = True
End If

For Each ws In Worksheets
If ws.Index <> wsA.Index And ws.Name <> "Merged Data" And ws.Name <> "Stats" And ws.Name <> "BlankTS" And ws.Name <> "BlankChkpt" And ws.Name <> "Summary" And ws.Name <> "DATES" And ws.Name <> "Holiday Costs(1)" And ws.Name <> "Holiday Stats(1)" And ws.Name <> "Holiday" And ws.Name <> "Cover" Then
r = Range("B65536").End(xlUp).Row + 1
wsA.Hyperlinks.Add Anchor:=wsA.Cells(r, 2), Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
wsA.Cells(r, 3).Value = ws.Range(MyTot1).Value
wsA.Cells(r, 4).Value = ws.Range(MyTot2).Value
wsA.Cells(r, 5).Value = ws.Range(MyTot3).Value
wsA.Cells(r, 6).Value = ws.Range(MyTot4).Value
wsA.Cells(r, 7).Value = ws.Range(MyTot5).Value
wsA.Cells(r, 8).Value = ws.Range(MyTot6).Value
wsA.Cells(r, 9).Value = ws.Range(MyTot7).Value
wsA.Cells(r, 10).Value = ws.Range(MyTot8).Value
wsA.Cells(r, 11).Value = ws.Range(MyTot9).Value
wsA.Cells(r, 12).Value = ws.Range(MyTot10).Value
End If
Next ws
Worksheets("Stats").Activate
Sheets("Stats").Move After:=Sheets("Cover")
Worksheets("Stats").Cells.Select
Columns("A:C").EntireColumn.AutoFit
'
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
R
  • Question Question
Macro not working after adding two new items
Replies
5
Views
459
regan_2000
R
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M

Forum statistics

Threads
1,224,595
Messages
6,179,799
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top