Select "OK" in sort warning box

[Muleskin57]

I'm running a large macro that reformats a data dump from an accounting system. It sorts several times and subtotals. During the running of the macro, I get an "error" message that Excel cannot determine the column header row. I have been hitting ENTER ("OK") and the macro moves on until the next instance of that command and I must hit ENTER or select "OK" for it to complete. How can I automate this macro completely, getting the offending stoppages to cease?

 

[Fluff]

Difficult to say without seeing the code.

 

[Muleskin57]

I think it emanates from this sub-macro:

Sub Sort_Programs()
'
' Sort_Programs Macro
'


'
    Sheets("DATA DUMP").Select
    Cells.Select
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "i2:i3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "k2:k3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "l2:l3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "j2:j3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DATA DUMP").Sort
        .SetRange Range("A1:WWW3002")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("DATA DUMP (Exp)").Select
    Cells.Select
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "i2:i926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "k2:k926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "l2:l926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "j2:j926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort
        .SetRange Range("A1:AO926")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("DATA DUMP").Select
End Sub

 

[Fluff]

I can see nothing in that code that would give the problemyou have described.
What exactly dose the error message say?

 

[Muleskin57]

"Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command.

-If you want the first row of the selection or list used as labels and not as data, click OK.
-if you selected a subset of cells in error, select a single cell, and try the command again.
-To create column lables, click Cancel, and enter a text label at the top of each column of data.
-For information about creating labels that are easy to detect, click Help.

OK Cancel Help"


OK is the correct choice and is the default, so I manually hit Enter.

 

[Fluff]

I cannot replicate that error, with the code you've supplied.
Do you have any merged cells?

 

[Muleskin57]

Hmmm. Maybe I have the wrong sub-macro. Let me look more. And thank you for your help, BTW.

 

[Muleskin57]

Thank you. I crashed the macro and the debugger landed me in this sub-macro. It has the right number of Subtotal commands for the number of error boxes that appear.

Sub Subtotal_Prog()
'
' Subtotal_Prog Macro
'


'
Sheets("DATA DUMP").Select
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
Sheets("DATA DUMP (Exp)").Select
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 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), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
Sheets("DATA DUMP").Select
End Sub

 

[Fluff]

I've never used grouped rows/columns, so unfortunately cannot help any further.
Hopefully somebody else will step in.

 

[Fluff]

Having played around a bit, do you have a header row?