I have an excel spreadsheet that gets exported out of MS Access and then updated using MS Access VBA.
The output of the file is:
MANAGER|PROGRAM|REP|TOTAL DIAL\TOTAL TT|AVG DIAL|AVG TT
I am trying to add subtotals but would like Columns 4, 5 to be xlSum and Columns 6, 7 to be XLAverage.
I tried setting the replace to False on the second pass with no luck.
I appreciate any help on this.
Thanks
Code:
If .ProtectContents = False Then 'Worksheets("qry_ExportCopy_Final").UsedRange.Select
ActiveSheet.Range("1:1").Font.Bold = True
ActiveSheet.Range("1:1").EntireColumn.AutoFit
'ActiveSheet.Cells.SpecialCells(xlBlanks).Value = 0
ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss"
ActiveSheet.Range("G:G").NumberFormat = "[h]:mm:ss"
'ActiveSheet.Range("S:U").NumberFormat = "[h]:mm:ss"
'ActiveSheet.Columns("F").TextToColumns Destination:=.Range("F1:F20000"), DataType:=xlDelimited
'ActiveSheet.Columns("G").TextToColumns Destination:=.Range("G1:G20000"), DataType:=xlDelimited
'ActiveSheet.Columns("J").TextToColumns Destination:=.Range("J1:J20000"), DataType:=xlDelimited
'ActiveSheet.Columns("K").TextToColumns Destination:=.Range("K1:K20000"), DataType:=xlDelimited
Selection.Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5), Replace:=True
Selection.Subtotal Groupby:=2, Function:=xlAvgerage, TotalList:=Array(6, 7), Replace:=False