1

I have a Macro I created to unprotect a sheet, deselect blanks, sort a column, re apply the blanks, and then re protect a sheet. The code works excellently on my computer, but when I try opening it on other computers, I get

an error code 438.

NOTE: I need to select blanks because I'm trying to sort columns with a formula. The columns will not sort correctly because the formula displays blanks in areas where information is missing.

All computers have the same operating system, the same version of excel, and the file is saved on a shared drive, so nothing should be changing. I've made sure that I enabled macros on each computer, but it still does not work.

Below is the code. Does anyone see any glaringly obvious issues I'm missing? I'm new to VBA and Macros but I've done a fair amount of reading and researching and cannot seem to figure this out.

Below is the updated code:

Sub Short2Long()
'
' Short2Long Macro
'

'
    Worksheets("Reunification").Unprotect
    Worksheets("Reunification").ListObjects("Reunification_Main").Range.AutoFilter Field:=1, _
        Criteria1:="<>"
    ThisWorkbook.Worksheets("Reunification").ListObjects("Reunification_Main"). _
        Sort.SortFields.Clear
    ThisWorkbook.Worksheets("Reunification").ListObjects("Reunification_Main"). _
        Sort.SortFields.Add2 Key:=Range("Reunification_Main[[#All],[Days in Care]]") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ThisWorkbook.Worksheets("Reunification").ListObjects( _
        "Reunification_Main").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Worksheets("Reunification").ListObjects("Reunification_Main").Range.AutoFilter Field:=1
    Worksheets("Reunification").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
        AllowSorting:=True, AllowFiltering:=True
klive17
  • 13
  • 4
  • Which line is highlighted in yellow, after the error is seen? And which one is the `ActiveSheet` on the other computers, after the vba code runs? – Vityata Feb 21 '19 at 14:37
  • Is there a way I can show which section highlights yellow? Its lines 6 to 9 that starts with ActiveWorkbook.Worksheet and ends xlSortTextAsNumbers. The active sheet should be called "Reunification" – klive17 Feb 21 '19 at 14:45
  • 1
    When the code runs are there any other workbooks open? – Error 1004 Feb 21 '19 at 14:49
  • No, just the one. – klive17 Feb 21 '19 at 14:50
  • When you open up the References of the .xlsm file (VBA editor -> Tools -> References), note all the references that are checked (active). Now go to one of the non-working computers and do the same thing. Is there a difference in which references are checked between the two? – TylerH Feb 21 '19 at 17:00
  • I can confirm there were differences. I made the edits so that both were the same, and it had no effect. Visual Basic for Applications, Microsoft Excel 16.0 Object Library, OLE Automation, Microsoft Office 16.0 Object Library, and Microsoft Forms 2.0 Object Library were selected. – klive17 Feb 21 '19 at 17:20

1 Answers1

0

Finally figured out the issue. It had to do with Excel 365 vs 2010 issues.

Excel 365 expects:

Sort.SortFields.Add2

But Excel 2010 expects:

Sort.SortFields.Add

Removing the 2 fixed the issue despite the fact that all computers were running Excel 365.

klive17
  • 13
  • 4