I created a macro that will sort and subtotal information. I then added a
formula that will calculate the grand totals of all the subtotals and
multiply by a number I indicated above. The problem: If I have more or less
rows of info than I did when I created the macro, my formula is messed up
because the grand total moved up or down a rows while my formula is reading
one particular space. Any way to get the grand totals fixed in one location
regardless of rows the macro is adding?

Thanks!!

why don't you post the macro code so folks can look at it

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=26804

Here it is. I'd like the Grand totals to consistently show up on row 126. 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


"duane" wrote:


why don't you post the macro code so folks can look at it?


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=268046