I am trying to write an Excel macro that does the following 2 things for each sheet in the currently open workbook:
- format column D, which is a date column
- total up column K, regardless of how many rows there are
Here is my code:
Dim ws As Worksheet, columnSum, lastRowInColK, totalRow
For Each ws In ActiveWorkbook.Worksheets
' format order date column
ws.Columns("D:D").NumberFormat = "mm/dd/yyyy;@"
' find the last row in column K, go down 2 rows, insert the sum of column K
Dim LastRowK As Long
LastRowK = Range("K" & Rows.Count).End(xlUp).Row
Range("K" & (LastRowK + 2)).Select
Selection.FormulaR1C1 = "=SUM(R[-" & (LastRowK - 1) & "]C:R[-1]C)"
The date formatting is working fine but the totaling of column K is not working. I'm getting a total, but it's happening only on the first worksheet. So, for example, if my workbook has 5 worksheets in it, This code inserts the sum of column K 5 times on the first worksheet and not at all on the other 4 worksheets.
This is driving me nuts. Msgbox tells me the name of each sheet and the date formatting in column D is happening on each worksheet. Can someone please tell me what I've missed?