I've manipulated the original macro a bit. The grand total is showing up on
row 126 but another grand total is showing up on various rows depending upon
how much data I put in the temp. I want to delete the other grand total... I
only want it on row 126 (columns b-k). Here's my macro, just in case. What
can i do to fix this?! Thanks.


Range("A16:K120").SOrt _
Key1:=Range("A16"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A15:K120").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(2, 3, 4, 5, 6, 7, 8, 9, 11), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Range("B128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
With Range("C128")
.Style = "Currency"
.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
End With
Range("D126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K126").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A128").Value = "Totals"
Range("B128").Value = ""
Range("B126").Formula = "=" & _
Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Offset(0, 10).Address
Set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.Offset(0, 1).Resize(1, 10).Copy
Range("B126").PasteSpecial (xlValues)
End Sub

"Tom Ogilvy" wrote:

Or perhaps you could do this

set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.offset(0,1).Resize(1,10).copy
Range("B126").Pastespecial(xlValues)

--
Regards,
Tom Ogilvy

"Amber M" wrote in message
...
I need the Grand Total to always show up on B126 through K126 but it will
only put a grand total in K126. I see in my macro why, but I dont know how
to
correctly fix it. Help! :)


Sub Piece()
Range("A14:K120").Sort _
Key1:=Range("A14"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A13:K120").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(2, 3, 4, 5, 6, 7, 8, 9, 11), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Range("B128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
With Range("C128")
.Style = "Currency"
.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
End With
Range("D128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A131").Value = "Totals"
Range("B131").Value = ""
Range("K126").Formula = "=" & _
Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Offset(0, 10).Address
Range("A131").Select
End Sub