Macro subtotals fixed on one row

A

Amber M

I'd like the Grand totals to consistently show up on row 126 but they move
around according to how much data I have in the range to sort and subtotal.
Any
ideas?

Sub Piece()
'
' Piece Macro
' Macro recorded 12/10/2004 by Gayle Morris
'
' Keyboard Shortcut: Ctrl+p
'
Range("A14:K120").Select
Selection.Sort Key1:=Range("A14"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A13:K120").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3,
4, 5, _
6, 7, 8, 9, 11), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=3
Range("B128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("C128").Select
Selection.Style = "Currency"
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("D128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C)"
Range("K129").Select
ActiveWindow.SmallScroll Down:=12
Range("A131").Select
ActiveCell.FormulaR1C1 = "Totals"
Range("B131").Select
ActiveCell.FormulaR1C1 = ""
Range("A131").Select
End Sub
 
A

Amber M

Hi Frank,

I don't want to enter them manually because it will take too long. I'm
creating a template and a macro that will be used 50-75 times every couple of
weeks. Adding the formula manually every time will take too much time.

Frank Kabel said:
Hi
then why not enter your formula smanually in row 126?

--
Regards
Frank Kabel
Frankfurt, Germany

Amber M said:
I'd like the Grand totals to consistently show up on row 126 but they move
around according to how much data I have in the range to sort and subtotal.
Any
ideas?

Sub Piece()
'
' Piece Macro
' Macro recorded 12/10/2004 by Gayle Morris
'
' Keyboard Shortcut: Ctrl+p
'
Range("A14:K120").Select
Selection.Sort Key1:=Range("A14"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A13:K120").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3,
4, 5, _
6, 7, 8, 9, 11), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=3
Range("B128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("C128").Select
Selection.Style = "Currency"
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("D128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C)"
Range("K129").Select
ActiveWindow.SmallScroll Down:=12
Range("A131").Select
ActiveCell.FormulaR1C1 = "Totals"
Range("B131").Select
ActiveCell.FormulaR1C1 = ""
Range("A131").Select
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

Macro to shift to next row 2
More Grand Totals 5
Totals An Easier Way?? 5
how do I write a loop macro? 9
VBA Code To have a macro repeat on all sheets in a workbook 3
Creating Pivot Table with Macro using Multiple Sheets 0
Macro Flexability 7
Run macro on selected sheets 10

Top