CODEWITHSUNDEEP
vbaexcel
Lillian
Lillian

Reputation: 51

Run-time error 91

I'm having a run-time error 91 and I have no idea why. I used this code for a different workbook and it works perfectly, the information in the columns are different that I'm extracting but I changed all the columns and ranges to the correct one, but now I'm getting this error here and the only difference is the Range, please help!

Range(Cells(20, 1), Cells(LastRow, LastCol)).Select
    Selection.AutoFilter
    Range("C2").Select

That is the beginning but here is where the error occurs:

ActiveWorkbook.Worksheets(msheet).AutoFilter.Sort.SortFields. _
        Add Key:=Range("A20:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

Please help, I don't know what I'm missing. Thanks!

Here is the beginning of the code:

Sub getdata()
Dim mastername As String
Dim count As Long
Dim match As Long
Dim repeat As Long
Dim path As String
Dim status As String
Dim name As String
Dim mpath As String
Dim cpath As String
Dim LastRow As Long
Dim LastCol As Integer
Dim mbank As String
Dim mname As String

mpath = Sheets("Master log").Cells(14, "W").Value
mname = Sheets("Master log").Cells(15, "W").Value
msheet = Sheets("Master log").Cells(16, "W").Value
Sheets("MGPR1").Range("A1:AA50000").ClearContents

name = Application.ActiveWorkbook.name
cpath = Application.ActiveWorkbook.path & "\"

Windows(name).Activate

'--open Management report  workbook if not already open
If CheckFileIsOpen(mname) = False Then
Workbooks.Open mpath & mname
End If
'-------------------------------------------

Windows(mname).Activate
    Sheets(msheet).Select

    'select full data

    With ActiveSheet
        LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
    End With

    With ActiveSheet
       ' LastCol = .Cells(1, .Columns.count).End(xlToLeft).Column
       LastCol = 20
    End With

Upvotes: 0

Views: 881

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

you have to reference the sheet of the range("A20.....") or else its in activesheet.

Something like sh.range("...") , or use a with section like this example:

with ActiveWorkbook.Worksheets(msheet)
    .AutoFilter.Sort.SortFields. _
    Add Key:= .Range("A20:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
end with

also to help referencing, and stop using activate/select :

 Dim Wb as Workbook
 Dim Sh as Worksheet

 'other code, i just write the needed code now
 set wb = Workbooks.Open mpath & mname
 set Sh = wb.Sheets(msheet)

 with Sh

     LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
     'lots of stuff to do (....) please wait , computing .... error / no really i try to be serious here!
 end with

Upvotes: 1

user4232746
user4232746

Reputation:

The meaning of the error is:

91 - Object variable not set

It is obtuse the chain of objects involved. This is a Recorded Macro that does the type of thing you are trying to do. If in doubt unwind a complex statement into simplier ones.

Also always do a Msgbox Vartype(whatever):Msgbox IsEmpty(whatever):msgbox IsNull(whatever). This allows you to check assumptions on what something is.

Range("A1:G19").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<>sub", Operator:=xlAnd
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
Range("H34").Select

Upvotes: 0

Related Questions