Excel insists on trying to repair a super basic workbook and worksheet. What can I try or look for?

[OaklandJim]

Team Excel. What am I doing wrong? This is not the first time I've gotten this type of error but this time it seems goofy. That said, there is almost always an explanation. Like I said in the title, the workbook I have is totally basic. It contains three very basic subs, a small amount of data, and nothing exotic. It is a workbook I saved so I don't have to remember how to do a sort (I'm old and my memory is not that great). The dialogs involved are shown in pictures.

_Sorting.xlsm
	C	D	E	F	G	H	I	J	K	L
2	Sorted Data							Unsorted Data
3	Category	Type	Number					Category	Type	Number
4	Cars	Body	4					Stuff	Truck	8
5	Cold	Body	2					Toys	Floor	0
6	More	Chair	8					More	Chair	8
7	Words	Cow	5					Girls	Door	9
8	Junk	Deer	2					Junk	Deer	2
9	Girls	Door	9					Words	Cow	5
10	Zoo	Door	6					Zoo	Door	6
11	Apple	Door	1					Less	Food	0
12	Toys	Floor	0					Cars	Body	4
13	Less	Food	0					Boys	Seat	6
14	Boys	Seat	6					Apple	Door	1
15	Stuff	Truck	8					Cold	Body	2
Sheet1

Option Explicit

Sub ResetThreeColumns()

    With Worksheets("Sheet2")
        .Range("J4:L15").Copy .Range("C4")
    End With

End Sub

Option Explicit

Sub TestCallerSort()

    Dim rSortRange As Range

    Set rSortRange = ThisWorkbook.Worksheets("Sheet2") _
        .Range("ThreeColumnRangeToSort_WithHeader")
        
    Call BasicSort(rSortRange, 2, 2)

End Sub


Sub BasicSort( _
    prSortRangeWithHeaders As Range, _
    piKey1Col As Long, _
    Optional piKey2Col As Long = 1, _
    Optional pbAscending As Boolean = True)
    
        Dim vAscending As Variant
        
        If pbAscending _
         Then
            vAscending = xlAscending
        Else
            vAscending = xlDescending
        End If

        prSortRangeWithHeaders.Parent.Sort.SortFields.Clear

        prSortRangeWithHeaders.Sort _
            Key1:=prSortRangeWithHeaders.Cells(2, piKey1Col), _
            Key2:=prSortRangeWithHeaders.Cells(2, piKey2Col), _
            Order1:=vAscending, _
            Header:=xlYes
End Sub

 

[Fluff]

Do you have any other code in that workbook?
You should not get that error when using Range.Sort, only when using Worksheet.Sort.
Also the sheet2.xml has nothing to do with the sheet name (or code name) it's the sheet index number, so it means the 2nd sheet in the workbook.

 

[OaklandJim]

Thanks very much for the response!

That is the only code.

I guess that I misunderstand this

You should not get that error when using Range.Sort, only when using Worksheet.Sort.

If I change code to clear the sort by referring to the range and not the range's parent I get an "Unanble to get the Sort property of the Range Class error.

prSortRangeWithHeaders.Parent.Sort.SortFields.Clear

I removed Sheet2 -- which had totally unexotic data -- and I got the same dialogs with only Sheet1. I deleted all rows and columns adjacent to the totally simple data and now the issue is resolved. I still wonder what I did in Sheet 2 to cause that error. I hope to avoid it in the future.

 

[Fluff]

This line of code is not needed as you are using Range.Sort

prSortRangeWithHeaders.Parent.Sort.SortFields.Clear

Also as I said before the name of the sheet has nothing to do with sheet2.xml