Sub-Totaling

G

Guest

I have built a template for the Accounts Receivables department. This is for
tracking agings of money owed to us from the status of Current to 360 days
past due. I built a Database to pull the info from our AS/400 system from
which the Credit Managers (CM) export to Excel. The template Sub-Totals by
Customer Number, and there might be 5 to 60 companies that it sub-totals. I
have to sub by customer number, but when I do this it leaves out the Customer
name, reference number, branch number, etc on the second tab. The CM’s then
have to go to the third tab to copy the rest of the info and paste it in the
second tab. A big pain. I have written some VB in another project to due
these calculations , but these scripts give all the info on all the Cm’s not
just individually that I need. I would like to just export the info to the
desktop and then use the subtotal function with all the info on the second
tab. Is these possible? I will list the fields names and also list my VB
Thanks Todd

co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date,
due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180,
181-360, 360+, CA,CM, TOTAL RESERVES



Sub TOTALRESERVETEMPLATE()
'
' TOTALRESERVETEMPLATE Macro
' 7/25/2005 by tmaxwell
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=43
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=57
ActiveWindow.LargeScroll Down:=3
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=47
ActiveWindow.SmallScroll Down:=-59
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 7
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 15.86
Range("I:I,L:L,M:M,N:N,O:O,P:p,Q:Q,R:R,U:U").Select
Range("U1").Activate
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
ActiveWindow.SmallScroll ToRight:=2
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
Selection.Copy
ActiveWindow.ScrollRow = 1711
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=130
ActiveWindow.SmallScroll Down:=-2
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=0
ActiveWindow.LargeScroll Down:=2
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.LargeScroll Down:=-2
ActiveWindow.SmallScroll Down:=7
Range("U2:U6001").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollColumn = 1
Cells.Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13,
14, _
15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Columns("C:C").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollRow = 6004
Range("A1:U6034").Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 1
Columns("D:H").Select
Selection.EntireColumn.Hidden = True
End Sub
 
D

Dave Peterson

You may want to consider using a pivottable to do your summary.

After you get it working manually, you can toss that version and record a macro
when you do it for real.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
I have built a template for the Accounts Receivables department. This is for
tracking agings of money owed to us from the status of Current to 360 days
past due. I built a Database to pull the info from our AS/400 system from
which the Credit Managers (CM) export to Excel. The template Sub-Totals by
Customer Number, and there might be 5 to 60 companies that it sub-totals. I
have to sub by customer number, but when I do this it leaves out the Customer
name, reference number, branch number, etc on the second tab. The CM’s then
have to go to the third tab to copy the rest of the info and paste it in the
second tab. A big pain. I have written some VB in another project to due
these calculations , but these scripts give all the info on all the Cm’s not
just individually that I need. I would like to just export the info to the
desktop and then use the subtotal function with all the info on the second
tab. Is these possible? I will list the fields names and also list my VB
Thanks Todd

co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date,
due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180,
181-360, 360+, CA,CM, TOTAL RESERVES

Sub TOTALRESERVETEMPLATE()
'
' TOTALRESERVETEMPLATE Macro
' 7/25/2005 by tmaxwell
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=43
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=57
ActiveWindow.LargeScroll Down:=3
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=47
ActiveWindow.SmallScroll Down:=-59
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 7
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 15.86
Range("I:I,L:L,M:M,N:N,O:O,P:p,Q:Q,R:R,U:U").Select
Range("U1").Activate
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
ActiveWindow.SmallScroll ToRight:=2
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
Selection.Copy
ActiveWindow.ScrollRow = 1711
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=130
ActiveWindow.SmallScroll Down:=-2
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=0
ActiveWindow.LargeScroll Down:=2
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.LargeScroll Down:=-2
ActiveWindow.SmallScroll Down:=7
Range("U2:U6001").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollColumn = 1
Cells.Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13,
14, _
15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Columns("C:C").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollRow = 6004
Range("A1:U6034").Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 1
Columns("D:H").Select
Selection.EntireColumn.Hidden = True
End Sub
Click to expand...
 
G

Guest

Thanks for resonding. Because I have Percentages reductions of 100, 50, 20,
10 on dollars collected when they account reach 60, 90, 180, 360 days,
getting all the calculations in the right place is a liitle like trading one
old horse for another. I have a number of P-tables doing various things, I
take a look. Thanks Dave

Dave Peterson said:
You may want to consider using a pivottable to do your summary.

After you get it working manually, you can toss that version and record a macro
when you do it for real.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
I have built a template for the Accounts Receivables department. This is for
tracking agings of money owed to us from the status of Current to 360 days
past due. I built a Database to pull the info from our AS/400 system from
which the Credit Managers (CM) export to Excel. The template Sub-Totals by
Customer Number, and there might be 5 to 60 companies that it sub-totals. I
have to sub by customer number, but when I do this it leaves out the Customer
name, reference number, branch number, etc on the second tab. The CM’s then
have to go to the third tab to copy the rest of the info and paste it in the
second tab. A big pain. I have written some VB in another project to due
these calculations , but these scripts give all the info on all the Cm’s not
just individually that I need. I would like to just export the info to the
desktop and then use the subtotal function with all the info on the second
tab. Is these possible? I will list the fields names and also list my VB
Thanks Todd

co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date,
due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180,
181-360, 360+, CA,CM, TOTAL RESERVES

Sub TOTALRESERVETEMPLATE()
'
' TOTALRESERVETEMPLATE Macro
' 7/25/2005 by tmaxwell
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=43
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=57
ActiveWindow.LargeScroll Down:=3
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=47
ActiveWindow.SmallScroll Down:=-59
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 7
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 15.86
Range("I:I,L:L,M:M,N:N,O:O,P:p,Q:Q,R:R,U:U").Select
Range("U1").Activate
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
ActiveWindow.SmallScroll ToRight:=2
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)"
Range("U2").Select
Selection.Copy
ActiveWindow.ScrollRow = 1711
ActiveWindow.SmallScroll Down:=1
ActiveWindow.LargeScroll Down:=130
ActiveWindow.SmallScroll Down:=-2
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=0
ActiveWindow.LargeScroll Down:=2
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.LargeScroll Down:=-2
ActiveWindow.SmallScroll Down:=7
Range("U2:U6001").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollColumn = 1
Cells.Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13,
14, _
15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Columns("C:C").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollRow = 6004
Range("A1:U6034").Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 1
Columns("D:H").Select
Selection.EntireColumn.Hidden = True
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macros in excel 2000 12
Help with Macro 3
How to get a macro to run on all files in a folder and save each one? 0
Macro - very slow run in 2003 3
Hard reference in a macro 1
Delete blank rows 5
toggeling Macro 3
Grouped Sheets and Formating 3

Top