In my macro I have rows of data that are automatically scanned and subtotaled
with a grand total below. Is there any way to make the Grand Totals
consistently show up on Row 25 regardless of how much data was subtotaled
(assuming the data didn't overflow into row 25)?

Thanks!

Hi
post your existing macro :-)

--
Regards
Frank Kabel
Frankfurt, Germany
"Amber M" schrieb im Newsbeitrag
...
In my macro I have rows of data that are automatically scanned and
subtotaled
with a grand total below. Is there any way to make the Grand Totals
consistently show up on Row 25 regardless of how much data was subtotaled
(assuming the data didn't overflow into row 25)?

Thanks!

Hi Frank,
It's a big macro. I need the Grand Total to ALWAYS show up on the same row,
let's pick row 100 as the default. The kicker is that I need formulas to
calculate below, and off of, that Grand Total. Here you go:

Thanks!

Sub FinishLabor()
'
' FinishLabor Macro
' Finish Labor Piece Breakdown Sheet
'
' Keyboard Shortcut: Ctrl+f
'
Range("A16:K90").Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A15:K80").Select
Range("K80").Activate
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:=24
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveWindow.SmallScroll Down:=-15
Range("A89").Select
ActiveCell.FormulaR1C1 = "Totals"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B89").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-75]C)"
Range("B89").Select
Selection.AutoFill Destination:=Range("B89:I89"), Type:=xlFillDefault
Range("B89:I89").Select
Range("K89").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C)"
Range("K90").Select
ActiveWindow.SmallScroll Down:=3
Range("B7:C7").Select
Selection.Copy
Range("A91").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A92").Select
ActiveCell.FormulaR1C1 = "Pay"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B9:C9").Select
Selection.Copy
Range("A94").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A95").Select
ActiveCell.FormulaR1C1 = "Pay"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A92").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A95").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=3
Range("A97").Select
ActiveWindow.SmallScroll Down:=-9
Range("B11:C11").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=15
Range("A97").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A98").Select
ActiveCell.FormulaR1C1 = "Pay"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[4])"
Range("B93").Select
ActiveWindow.SmallScroll Down:=3
Range("B95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[4])"
Range("B96").Select
ActiveWindow.SmallScroll Down:=6
Range("B98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[4])"
Range("A98").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B92").Select
Range("C92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[3])"
Range("D92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[2])"
Range("E92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[1])"
Range("F92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C)"
Range("G92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[-1])"
Range("H92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[-2])"
Range("I92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[-3])"
Range("K92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-85]C[-5])"
Range("K93").Select
ActiveWindow.SmallScroll Down:=3
Range("C95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[3])"
Range("D95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-86]C[2])"
Range("E95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-86]C[1])"
Range("F95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C*R[-86]C)"
Range("G95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[-1])"
Range("H95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[-2])"
Range("I95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[-3])"
Range("K95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[-5])"
Range("D95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[2])"
Range("E95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C[1])"
Range("F95").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C*R[-86]C)"
Range("K95").Select
ActiveWindow.SmallScroll Down:=3
Range("C98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[3])"
Range("D98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[2])"
Range("E98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[1])"
Range("F98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C)"
Range("G98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[-1])"
Range("H98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[-2])"
Range("I98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[-3])"
Range("K98").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C*R[-87]C[-5])"
Range("K99").Select
ActiveWindow.SmallScroll Down:=-6
Range("H7:I7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[85]C[-6]:R[85]C[3])"
Range("H9:I9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[86]C[-6]:R[86]C[3])"
Range("H11:I11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[87]C[-6]:R[87]C[3])"
Range("H12").Select
ActiveWindow.SmallScroll Down:=-3
Range("A91:B91").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("A94:B94").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("A97:B97").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("B92:I98,K40:K98").Select
Range("K98").Activate
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
Range("B89:I89").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
Range("F100").Select
ActiveWindow.SmallScroll Down:=-21
Range("B92:I92,B95:I95,B98:I98").Select
Range("B98").Activate
Selection.Font.Bold = True
Range("A40:J120").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Range("E87").Select
ActiveWindow.SmallScroll Down:=12
Range("A92,A95,A98").Select
Range("A98").Activate
Selection.Font.Bold = True
Range("K92,K96,K95,K98").Select
Range("K98").Activate
Selection.Font.Bold = True
Range("E92").Select
ActiveWindow.SmallScroll Down:=-18
End Sub


"Frank Kabel" wrote:

Hi
post your existing macro :-)

--
Regards
Frank Kabel
Frankfurt, Germany
"Amber M" schrieb im Newsbeitrag
...
In my macro I have rows of data that are automatically scanned and
subtotaled
with a grand total below. Is there any way to make the Grand Totals
consistently show up on Row 25 regardless of how much data was subtotaled
(assuming the data didn't overflow into row 25)?

Thanks!