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