BrennieB
New Member
 Joined
 Sep 28, 2021
 Messages
 3
 Office Version

 2019
 Platform

 Windows
This question is compound, so I need to sum up the beginning before I get to my question. I have a spreadsheet that is being split into multiple workbooks based on a column. Once this is done, I need to sum...let's say Column K in each worksheet at the last row in that column +2. I need the formula to show though, not the sum. I can not get the syntax correct. Any help would be appreciated. This is what I am working with (forgive the sloppy language, I am new at VBA) I have only included the part in which I am trying to fix...not the part before that splits it into workbooks:
lastrow = objSheet.Cells(Rows.Count, 11).End(xlUp).Row
lastrow1 = objSheet.Cells(Rows.Count, 14).End(xlUp).Row
lastrow2 = objSheet.Cells(Rows.Count, 15).End(xlUp).Row
lastrow3 = objSheet.Cells(Rows.Count, 16).End(xlUp).Row
lastrow4 = objSheet.Cells(Rows.Count, 17).End(xlUp).Row
lastrow5 = objSheet.Cells(Rows.Count, 18).End(xlUp).Row
objSheet.Range("J" & lastrow + 2) = "Total:"
objSheet.Range("J" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2) = Application.WorksheetFunction.Sum(objSheet.Range("K2:K" & lastrow)) This is where I am trying to produce the formula in the worksheet, not the sum
objSheet.Range("K" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2).NumberFormat = "$#,##0.00"
objSheet.Range("N" & lastrow1 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("N2:N" & lastrow1))
objSheet.Range("N" & lastrow1 + 2).Font.Bold = True
objSheet.Range("N" & lastrow1 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("O" & lastrow2 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("O2:O" & lastrow2))
objSheet.Range("O" & lastrow2 + 2).Font.Bold = True
objSheet.Range("O" & lastrow2 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("P" & lastrow3 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("P2:P" & lastrow3))
objSheet.Range("P" & lastrow3 + 2).Font.Bold = True
objSheet.Range("P" & lastrow3 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("Q" & lastrow4 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("Q2:Q" & lastrow4))
objSheet.Range("Q" & lastrow4 + 2).Font.Bold = True
objSheet.Range("Q" & lastrow4 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("R" & lastrow5 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("R2:R" & lastrow5))
objSheet.Range("R" & lastrow5 + 2).Font.Bold = True
objSheet.Range("R" & lastrow5 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("A:S").AutoFilter
objSheet.Name = objSheet.Range("A2")
Cells.SpecialCells(xlCellTypeVisible)(1).Select
lastrow = objSheet.Cells(Rows.Count, 11).End(xlUp).Row
lastrow1 = objSheet.Cells(Rows.Count, 14).End(xlUp).Row
lastrow2 = objSheet.Cells(Rows.Count, 15).End(xlUp).Row
lastrow3 = objSheet.Cells(Rows.Count, 16).End(xlUp).Row
lastrow4 = objSheet.Cells(Rows.Count, 17).End(xlUp).Row
lastrow5 = objSheet.Cells(Rows.Count, 18).End(xlUp).Row
objSheet.Range("J" & lastrow + 2) = "Total:"
objSheet.Range("J" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2) = Application.WorksheetFunction.Sum(objSheet.Range("K2:K" & lastrow)) This is where I am trying to produce the formula in the worksheet, not the sum
objSheet.Range("K" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2).NumberFormat = "$#,##0.00"
objSheet.Range("N" & lastrow1 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("N2:N" & lastrow1))
objSheet.Range("N" & lastrow1 + 2).Font.Bold = True
objSheet.Range("N" & lastrow1 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("O" & lastrow2 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("O2:O" & lastrow2))
objSheet.Range("O" & lastrow2 + 2).Font.Bold = True
objSheet.Range("O" & lastrow2 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("P" & lastrow3 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("P2:P" & lastrow3))
objSheet.Range("P" & lastrow3 + 2).Font.Bold = True
objSheet.Range("P" & lastrow3 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("Q" & lastrow4 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("Q2:Q" & lastrow4))
objSheet.Range("Q" & lastrow4 + 2).Font.Bold = True
objSheet.Range("Q" & lastrow4 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("R" & lastrow5 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("R2:R" & lastrow5))
objSheet.Range("R" & lastrow5 + 2).Font.Bold = True
objSheet.Range("R" & lastrow5 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("A:S").AutoFilter
objSheet.Name = objSheet.Range("A2")
Cells.SpecialCells(xlCellTypeVisible)(1).Select