Grand Totals @ Same Place
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! |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com