Hello,
I have a problem with running a macro that generates an XYScatterChart after sorting the data in the table initially. It sorts firstly by the column selected by user in input box, and then by the fixed column.
It runs on one of my computers with Excel for Microsoft 365 MSO (Version 2206 Build 16.0.15330.20306) 64-bit
The error pops up when I try to run this macro on a second computer with Excel 2016 (16.0.5356.1000) MSO (16.0.5361.1000) 32-bit
This is the part of the code where the error occurs (the bolded line generates an error)
*******************************************************************************************
If CategoryColumn = 3 Then
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields. _
Add2 Key:=Range("Table1[[#All],[Header3]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields. _
Add2 Key:=Range("Table1[[#All],[Header1]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
the code goes on...
******************************************************************************************
- when I recorded a macro for table sorting on the second computer the code !was exactly the same! and it worked.
- when I removed the line with .Clear, the same error occurred for the next line.
- I found there might be a problem with one of the updates, but this macro shall be used on many computers in my organization, so this is no option to uninstall updates.
Do you have an idea how to solve this issue?
