Method 'range' of object '_Global' failed - Re-activate previous ActiveCell

Question

I wrote this little procedure as part of a bigger project and wanted to make it more user-friendly by re-activating the last active cell prior to running the code. However, it gives me run-time error 1004: Method 'range' of object '_Global' failed.

I managed to re-activate the previous ActiveWorksheet, but how do I get it do re-activate the actual cell? I looked at the discussion here: Run-time error '1004' : Method 'Range' of object'_Global' failed

which led me to think that the problem was in the way I reference the OldActiveCell, but I still cannot find a solution.

Does anyone know the answer to this problem? Code below:

Sub SortData()
    'Sorts Data on Input Sheet; first after project ID and then position type
    Dim DataRange As Range
    Dim OldActiveSheet As Object
    Dim OldActiveCell As Object

    'Define variable for currently active cell to reactivate it afterwards
    Set OldActiveSheet = ActiveSheet
    Set OldActiveCell = ActiveCell
    Sheets("Input").Activate

    Range("DataRange").Activate
        ActiveSheet.Sort.SortFields.Clear
        'First filter criterium
        ActiveSheet.Sort.SortFields.Add Key:=Range("ProjectList") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'Second filter criterium
        ActiveSheet.Sort.SortFields.Add Key:=Range("PositionType") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        'Loop through and sort
        With ActiveSheet.Sort
            .SetRange Range("DataRange")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    'Reactivate previous active cell
    Range("OldActiveSheet.OldActiveCell").Activate
End Sub

You might also want to read this? stackoverflow.com/questions/10714251/… — Siddharth Rout

GSerg GSerg · Accepted Answer · 2013-01-01T14:46:33

Replace Range("OldActiveSheet.OldActiveCell").Activate with

OldActiveSheet.Activate
OldActiveCell.Activate

Apparently you thought Range("DataRange") works because you have a Range variable named DataRange. No, it works because you have a named range somewhere, named DataRange. Range won't pick up your local variables if you pass their names as strings. No method or procedure will.

Method 'range' of object '_Global' failed - Re-activate previous ActiveCell

1 Answers