Need help adding sums to last row.

[flyers2thecup]

It's the time of year when I have to use the attached macro. This year there has been a lot of columns added...and when I added things to this...i apparently deleted something(s) that were doing what I wanted. I tried looking through old posts of mine, but couldn't find the answer.

Here is the code:
----------------------------------------
Sub Merit04()
'
' Merit04 Macro
' Macro recorded 4/6/2006 by xsxf8cq
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With


Columns("A:A").NumberFormat = "0"
Columns("B:B").NumberFormat = "@"
Columns("C:C").NumberFormat = "@"
Columns("D:D").NumberFormat = "@"
Columns("E:E").NumberFormat = "@"
Columns("E:E").HorizontalAlignment = xlCenter
Columns("F:F").NumberFormat = "@"
Columns("F:F").HorizontalAlignment = xlCenter
Columns("G:G").NumberFormat = "@"
Columns("G:G").HorizontalAlignment = xlCenter
Columns("H:H").NumberFormat = "#,##0"
Columns("H:H").HorizontalAlignment = xlRight
Columns("I:I").NumberFormat = "#,##0"
Columns("I:I").HorizontalAlignment = xlRight
Columns("J:J").NumberFormat = "@"
Columns("J:J").HorizontalAlignment = xlCenter
Columns("K:K").NumberFormat = "#0.000000000000000"
Columns("K:K").HorizontalAlignment = xlRight
Columns("L:L").NumberFormat = "#,##0"
Columns("L:L").HorizontalAlignment = xlRight
Columns("M:M").NumberFormat = "#,##0"
Columns("M:M").HorizontalAlignment = xlRight
Columns("N:N").NumberFormat = "@"
Columns("N:N").HorizontalAlignment = xlCenter
Columns("O:O").NumberFormat = "#0.000000000000000"
Columns("O:O").HorizontalAlignment = xlRight
Columns("P:P").NumberFormat = "#,##0"
Columns("P:P").HorizontalAlignment = xlRight
Columns("Q:Q").NumberFormat = "@"
Columns("Q:Q").HorizontalAlignment = xlCenter
Columns("R:R").NumberFormat = "mm/dd/yyyy"
Columns("R:R").HorizontalAlignment = xlCenter
Columns("S:S").NumberFormat = "#,##0"
Columns("S:S").HorizontalAlignment = xlRight
Columns("T:T").NumberFormat = "#,##0"
Columns("T:T").HorizontalAlignment = xlRight
Columns("U:U").NumberFormat = "#,##0"
Columns("U:U").HorizontalAlignment = xlRight
Columns("V:V").NumberFormat = "#,##0"
Columns("V:V").HorizontalAlignment = xlRight
Columns("W:W").NumberFormat = "#,##0"
Columns("W:W").HorizontalAlignment = xlRight
Columns("X:X").NumberFormat = "0.00%"
Columns("X:X").HorizontalAlignment = xlRight
Columns("Y:Y").NumberFormat = "@"
Columns("Y:Y").HorizontalAlignment = xlCenter
Columns("Z:Z").NumberFormat = "#,##0"
Columns("Z:Z").HorizontalAlignment = xlRight
Columns("AA:AA").NumberFormat = "0.00%"
Columns("AA:AA").HorizontalAlignment = xlRight
Columns("AC:AC").NumberFormat = "@"
Columns("AC:AC").HorizontalAlignment = xlCenter
Columns("AD:AD").NumberFormat = "#,##0"
Columns("AD:AD").HorizontalAlignment = xlRight
Columns("AE:AE").NumberFormat = "#,##0"
Columns("AE:AE").HorizontalAlignment = xlRight
Columns("AF:AF").NumberFormat = "@"
Columns("AF:AF").HorizontalAlignment = xlCenter
Columns("AG:AG").NumberFormat = "#0.000000000000000"
Columns("AG:AG").HorizontalAlignment = xlRight
Columns("AH:AH").NumberFormat = "#,##0"
Columns("AH:AH").HorizontalAlignment = xlRight
Columns("AI:AI").NumberFormat = "#,##0"
Columns("AI:AI").HorizontalAlignment = xlRight
Columns("AJ:AJ").NumberFormat = "@"
Columns("AJ:AJ").HorizontalAlignment = xlCenter
Columns("AK:AK").NumberFormat = "#0.000000000000000"
Columns("AK:AK").HorizontalAlignment = xlRight
Columns("AL:AL").NumberFormat = "#,##0"
Columns("AL:AL").HorizontalAlignment = xlRight
Columns("AM:AM").NumberFormat = "@"
Columns("AM:AM").HorizontalAlignment = xlCenter
Columns("AN:AN").NumberFormat = "mm/dd/yyyy"
Columns("AN:AN").HorizontalAlignment = xlCenter
Columns("AO:AO").NumberFormat = "#,##0"
Columns("AO:AO").HorizontalAlignment = xlRight
Columns("AP:AP").NumberFormat = "@"
Columns("AP:AP").HorizontalAlignment = xlCenter
Columns("AQ:AQ").NumberFormat = "#0.000000000000000"
Columns("AQ:AQ").HorizontalAlignment = xlRight
Columns("AR:AR").NumberFormat = "#,##0"
Columns("AR:AR").HorizontalAlignment = xlRight
Columns("AS:AS").NumberFormat = "#,##0"
Columns("AS:AS").HorizontalAlignment = xlRight
Columns("AT:AT").NumberFormat = "#,##0"
Columns("AT:AT").HorizontalAlignment = xlRight
Columns("AU:AU").NumberFormat = "@"
Columns("AU:AU").HorizontalAlignment = xlCenter
Columns("AV:AV").NumberFormat = "#0.000000000000000"
Columns("AV:AV").HorizontalAlignment = xlRight
Columns("AW:AW").NumberFormat = "#,##0"
Columns("AW:AW").HorizontalAlignment = xlRight
Columns("AX:AX").NumberFormat = "#,##0"
Columns("AX:AX").HorizontalAlignment = xlRight
Columns("AY:AY").NumberFormat = "@"
Columns("AY:AY").HorizontalAlignment = xlCenter
Columns("AZ:AZ").NumberFormat = "#0.000000000000000"
Columns("AZ:AZ").HorizontalAlignment = xlRight
Columns("BA:BA").NumberFormat = "#,##0"
Columns("BA:BA").HorizontalAlignment = xlRight
Columns("BB:BB").NumberFormat = "#,##0"
Columns("BB:BB").HorizontalAlignment = xlRight
Columns("BC:BC").NumberFormat = "#,##0"
Columns("BC:BC").HorizontalAlignment = xlRight
Columns("BD:BD").NumberFormat = "#,##0"
Columns("BD:BD").HorizontalAlignment = xlRight
Columns("BE:BE").NumberFormat = "@"
Columns("BE:BE").HorizontalAlignment = xlCenter
Columns("BF:BF").NumberFormat = "#0.000000000000000"
Columns("BF:BF").HorizontalAlignment = xlRight
Columns("BG:BG").NumberFormat = "#,##0"
Columns("BG:BG").HorizontalAlignment = xlRight
Columns("BH:BH").NumberFormat = "0.00%"
Columns("BH:BH").HorizontalAlignment = xlRight
Columns("BI:BI").NumberFormat = "0.00%"
Columns("BI:BI").HorizontalAlignment = xlRight
Columns("BK:BK").NumberFormat = "#,##0"
Columns("BK:BK").HorizontalAlignment = xlRight
Columns("BL:BL").NumberFormat = "0.00%"
Columns("BL:BL").HorizontalAlignment = xlRight
Columns("BN:BN").NumberFormat = "#,##0"
Columns("BN:BN").HorizontalAlignment = xlRight
Columns("BO:BO").NumberFormat = "#,##0"
Columns("BO:BO").HorizontalAlignment = xlRight
Columns("BP:BP").NumberFormat = "0.00%"
Columns("BP:BP").HorizontalAlignment = xlRight
Columns("BQ:BQ").NumberFormat = "#,##0"
Columns("BQ:BQ").HorizontalAlignment = xlRight
Columns("BR:BR").NumberFormat = "#,##0"
Columns("BR:BR").HorizontalAlignment = xlRight
Columns("BS:BS").NumberFormat = "0.00%"
Columns("BS:BS").HorizontalAlignment = xlRight
Columns("BU:BU").NumberFormat = "#,##0"
Columns("BU:BU").HorizontalAlignment = xlRight
Columns("BV:BV").NumberFormat = "#,##0"
Columns("BV:BV").HorizontalAlignment = xlRight
Columns("BW:BW").NumberFormat = "#,##0"
Columns("BW:BW").HorizontalAlignment = xlRight
Columns("BX:BX").NumberFormat = "#,##0"
Columns("BX:BX").HorizontalAlignment = xlRight
Columns("BZ:BZ").NumberFormat = "#,##0"
Columns("BZ:BZ").HorizontalAlignment = xlRight
Columns("CA:CA").NumberFormat = "@"
Columns("CA:CA").HorizontalAlignment = xlCenter
Columns("CB:CB").NumberFormat = "#0.000000000000000"
Columns("CB:CB").HorizontalAlignment = xlRight
Columns("CC:CC").NumberFormat = "#,##0"
Columns("CC:CC").HorizontalAlignment = xlRight
Columns("CD:CD").NumberFormat = "#,##0"
Columns("CD:CD").HorizontalAlignment = xlRight
Columns("CE:CE").NumberFormat = "@"
Columns("CE:CE").HorizontalAlignment = xlCenter
Columns("CF:CF").NumberFormat = "#0.000000000000000"
Columns("CF:CF").HorizontalAlignment = xlRight
Columns("CG:CG").NumberFormat = "#,##0"
Columns("CG:CG").HorizontalAlignment = xlRight
Columns("CI:CI").NumberFormat = "#,##0"
Columns("CI:CI").HorizontalAlignment = xlRight
Columns("CJ:CJ").NumberFormat = "#,##0"
Columns("CJ:CJ").HorizontalAlignment = xlRight
Columns("CK:CK").NumberFormat = "0.00%"
Columns("CK:CK").HorizontalAlignment = xlRight
Columns("CL:CL").NumberFormat = "#,##0"
Columns("CL:CL").HorizontalAlignment = xlRight
Columns("CM:CM").NumberFormat = "#,##0"
Columns("CM:CM").HorizontalAlignment = xlRight
Columns("CN:CN").NumberFormat = "0.00%"
Columns("CN:CN").HorizontalAlignment = xlRight


Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("P2:P" & LastRow).Formula = "=IF(ISERROR(M2*O2),0,(M2*O2))"
Range("W2:W" & LastRow).Formula = "=IF(ISERROR(S2+U2+V2),0, (S2+U2+V2))"
Range("X2:X" & LastRow).Formula = "=IF(ISERROR(W2/H2),0,(W2/H2))"
Range("AA2:AA" & LastRow).Formula = "=IF(ISERROR(Z2/L2),0,(Z2/L2))"
Range("AL2:AL" & LastRow).Formula = "=IF(ISERROR(AI2*AK2),0,(AI2*AK2))"
Range("BB2:BB" & LastRow).Formula = "=IF(ISERROR(AO2+AW2+BA2),0, (AO2+AW2+BA2))"
Range("BB2:BB" & LastRow).Formula = "=IF(ISERROR(AR2+AT2+AX2),0, (AR2+AT2+AX2))"
Range("BG2:BG" & LastRow).Formula = "=IF(ISERROR(BD2*BF2),0,(BD2*BF2))"
Range("BH2:BH" & LastRow).Formula = "=IF(ISERROR(BB2/AD2),0,(BB2/AD2))"
Range("BI2:BI" & LastRow).Formula = "=IF(ISERROR(BC2/AH2),0,(BC2/AH2))"
Range("BK2:BK" & LastRow).Formula = "=IF(ISERROR(W2-BB2),0, (W2-BB2))"
Range("BL2:BL" & LastRow).Formula = "=IF(ISERROR(BK2/BB2),0, (BK2/BB2))"
Range("BN2:BN" & LastRow).Formula = "=IF(ISERROR(H2+I2+W2),0, (H2+I2+W2))"
Range("BO2:BO" & LastRow).Formula = "=IF(ISERROR(AD2+AE2+AO2+AW2+BA2),0, (AD2+AE2+AO2+AW2+BA2))"
Range("BP2:BP" & LastRow).Formula = "=IF(ISERROR((BN2-BO2)/BO2),0,((BN2-BO2)/BO2))"
Range("BQ2:BQ" & LastRow).Formula = "=IF(ISERROR(L2+P2+Z2),0, (L2+P2+Z2))"
Range("BR2:BR" & LastRow).Formula = "=IF(ISERROR(AH2+AL2+BC2),0, (AH2+AL2+BC2))"
Range("BS2:BS" & LastRow).Formula = "=IF(ISERROR((BQ2-BR2)/BR2),0,((BQ2-BR2)/BR2))"
Range("CG2:CG" & LastRow).Formula = "=IF(ISERROR(CD2*CF2),0,(CD2*CF2))"
Range("CI2:CI" & LastRow).Formula = "=IF(ISERROR(BN2+BV2+BZ2),0, (BN2+BV2+BZ2))"
Range("CJ2:CJ" & LastRow).Formula = "=IF(ISERROR(BO2+BX2+CD2),0, (BO2+BX2+CD2))"
Range("CK2:CK" & LastRow).Formula = "=IF(ISERROR((CI2-CJ2)/CJ2),0,((CI2-CJ2)/CJ2))"
Range("CL2:CL" & LastRow).Formula = "=IF(ISERROR(BQ2+BV2+CC2),0, (BQ2+BV2+CC2))"
Range("CM2:CM" & LastRow).Formula = "=IF(ISERROR(BR2+BX2+CG2),0, (BR2+BX2+CG2))"
Range("CN2:CN" & LastRow).Formula = "=IF(ISERROR((CL2-CM2)/CM2),0,((CL2-CM2)/CM2))"

LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("S" & LastRow) = "=Sum(S2:S" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("AO" & LastRow) = "=Sum(AO2:AO" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("AR" & LastRow) = "=Sum(AR2:AR" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("AW" & LastRow) = "=Sum(AW2:AW" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BA" & LastRow) = "=Sum(BA2:BA" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BU" & LastRow) = "=Sum(BU2:BU" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BV" & LastRow) = "=Sum(BV2:BV" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BW" & LastRow) = "=Sum(BW2:BW" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BX" & LastRow) = "=Sum(BX2:BX" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("BZ" & LastRow) = "=Sum(BZ2:BZ" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("CC" & LastRow) = "=Sum(CC2:CC" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("CD" & LastRow) = "=Sum(CD2:CD" & LastRow - 1 & ")"
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("CG" & LastRow) = "=Sum(CG2:CG" & LastRow - 1 & ")"


Rows("1:1").Select
Range("E1").Activate
Selection.Font.Bold = True
Cells.Select
Range("E1").Activate
Selection.Columns.AutoFit
Range("E1").Select
Selection.EntireRow.Insert
Range("E1:Y1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Selection.Merge
ActiveCell.FormulaR1C1 = "2009 MERIT ELECTRONIC PLANNING WORKSHEET"
Range("A1:Y1").Select
Range("E1").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
End With

Selection.Merge
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With


Range("A2").Select
Rows(Cells(Rows.Count, 1).End(xlUp).Row).Font.Bold = True
End Sub
----------------------------------------
each file i start with will have a different amount of rows.
There are certain columns, S for example, that I would like to outline the column in bold, and have the sum of that column in bold font.

so, if there is data in S2:S14, S2:S15 should be outline in bold, and the formula results in S16 should be in bold font. Not sure if this is picky, but i'd only want the outer parts of the column bolded...not the horizontal lines in the middle...but S16 field should be bold borderd on all 4 sides.

Thank you!! (as always)

 

[flyers2thecup]

I also noticed that the SUMS aren't working. instead of having sums AFTER the data, it appears that i'm bolding the last row of the data.

What do I need to fix so my sum functions are AFTER the last row of data.

Thank you,
Angelo