Adding a sum formula to worksheets with variable rows

[BrennieB]

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

 

[Eric W]

Welcome to the MrExcel forum!

Try:

objSheet.Range("K" & lastrow + 2).Formula = "=Sum(K2:K" & lastrow & ")"

 

[BrennieB]

@Eric W , perfect!!!!! Worked beautifully.

 

[Eric W]

Happy to help! Thanks for the feedback. ?