I am using MS Access VBA to update excel spreadsheets after they have been exported from Access.
When I get to the section that subtotals the results I get the error message: Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command"
The exported spreadsheets have varying number of columns as each day new data is added. The data is a mixture of text, number and time, which is why you see me setting the column values to time below. Basically the data goes Text, text, text (these are stagnant and column 2 is used for grouping. The results data go in a pattern of number, time, time time, number, time, time, time etc
The error happens in this section of the vba. It has to do with a range I think but I can't figure it out.
Thanks for the help as always
Code:With mybook.Worksheets(1) If .ProtectContents = False Then Worksheets("qry_Daily_Export").UsedRange.Select ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("G:I").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("K:M").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("O:R").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("S:U").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("W:Y").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AA:AC").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AE:AG").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AI:AK").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AM:AO").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AQ:AS").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AU:AW").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AY:BA").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BC:BE").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BG:BI").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BK:BM").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BO:BQ").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BS:BU").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("BW:BY").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CA:CC").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CE:CG").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CI:CK").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CM:CO").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CQ:CS").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CU:CW").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("CY:DA").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DC:DE").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DG:DI").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DK:DM").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DO:DR").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DS:DU").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("DW:DY").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("EA:EC").NumberFormat = "[h]:mm:ss" Selection.Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133)