Sort method of Range class fails only on certain PCs accessing network file

44 Views Asked by At

Hopefully this doesn't get caught up as a rehash of the normal errors with Range and Sort. I have a workbook that was fully functional and lives on a networked storage. The PC that primarily accessed the file was always able to fully utilize the workbook and runs Excel 2013. Over the weekend, we tested an Office 365 deployment that we had to reverse. The PC had Office 365 installed, removed, and Office 2013 reinstalled with all the necessary VB packages. Now when that PC tries to work with the workbook, this code:

ActiveWorkbook.Sheets("Sales Log").Columns("A:P").Sort key1:=Sheets("Sales Log").Range("A1"), order1:=xlAscending, Header:=xlYes

Throws the dreaded "Run-Time Error 1004 Sort method of Range class failed"

The workbook still 100% works from a different PC that never had the 365 deployment and has had no configuration changes. I am at a complete loss. Can anyone advise? Thanks!

Here's the entire sub as requested: (it sorts a list and refreshes a pivottable)

Private Sub Worksheet_Activate()

    Dim activeFileName As String

    If ActiveWorkbook.Sheets("Control").Range("B17").Value = "Undeployed" Then
        Exit Sub
        Else:
    End If

    Worksheets("Sales Log").Unprotect
    If ActiveWorkbook.Sheets("Control").Range("B15").Value = "Single" Then
        ActiveWorkbook.Sheets("Sales Log").Columns("A:O").Sort key1:=Sheets("Sales Log").Range("A1"), order1:=xlAscending, Header:=xlYes
        Else:
        ActiveWorkbook.Sheets("Sales Log").Columns("A:P").Sort key1:=Sheets("Sales Log").Range("A1"), order1:=xlAscending, Header:=xlYes
    End If
    Worksheets("Sales Log").Protect

    ActiveWorkbook.Sheets("Tables").PivotTables("PivotTable1").RefreshTable

    activeFileName = ActiveWorkbook.Worksheets("Control").Range("B16").Value
    'Make sure your working file is not an autobackup or restore file
    If activeFileName <> ActiveWorkbook.Name Then
        Call wrongWorkingFile(activeFileName)
    End If

    Application.CutCopyMode = False

End Sub
0

There are 0 best solutions below