run-time error 91

[Pancakes1032]

Hi, 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!

[westconn1]

hard to guess without seeing all the code
does the sheet name in msheet exist?

[Paul_Hossler]

Roger westconn1's comment but using Cells() without a qualifying parent, will default to the active sheet, which might not be the one you want:




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



GUESSING here but maybe (note the 'dot' Cells, etc):



With ActiveWorkbook.Worksheets(msheet)
Range(.Cells(20, 1), .Cells(LastRow, LastCol)).Select
Selection.AutoFilter
.Range("C2").Select
End With

[westconn1]

(note the 'dot' Cells, etc):in that case would be .range(.cells........

[Pancakes1032]

That didn't work but here's a shell of the workbook I am working on. You can view the code there. I'm still getting the same error even with the change made.
in that case would be .range(.cells........

[Pancakes1032]

Roger westconn1's comment but using Cells() without a qualifying parent, will default to the active sheet, which might not be the one you want:




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



GUESSING here but maybe (note the 'dot' Cells, etc):



With ActiveWorkbook.Worksheets(msheet)
Range(.Cells(20, 1), .Cells(LastRow, LastCol)).Select
Selection.AutoFilter
.Range("C2").Select
End With


I tried that but still no luck. I uploaded the workbook though so you can see the full code.

[Pancakes1032]

This is the line of code that is highlighted, saying the saying the object variable or With block variable not set:

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

[westconn1]

if msheet is not the activesheet, range("a20:A" & LastRow), which refers to the activesheet, could be on a different worksheet and would give this error
similar as to paul's suggestion, but on a different line

[Pancakes1032]

if msheet is not the activesheet, range("a20:A" & LastRow), which refers to the activesheet, could be on a different worksheet and would give this error
similar as to paul's suggestion, but on a different line

How do I know if msheet is the activesheet? I got this code from someone else, I'm not really that pro at VBA, so I don't fully understand.

[westconn1]

How do I know if msheet is the activesheet?ideally always fully qualify your ranges like


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

[Aussiebear]

How do I know if msheet is the activesheet? I got this code from someone else, I'm not really that pro at VBA, so I don't fully understand.

You could simply go with Westconn1's code or you could declare the sheet required

With Worksheets("mSheet"}
.Autofilter.......etc.

[westconn1]

you would also get the same error if lastrow has no value and defaults to 0
as you have not posted your full code, we can not see what other errors maybe contributing to your problem

[Pancakes1032]

I tried both of those suggestions and they didn't work. I posted the full workbook and the full code is in there. Thanks for helping guys :)

[westconn1]

i am unable to determine your problem as my old version of excel does not support some of the code, i might test later on a different machine

[westconn1]

i have tested your workbook, the code runs from update master button without error, please indicate what input values cause problem or what sequence causes error to occur

[Paul_Hossler]

in that case would be .range(.cells........


Don't think that you would need the <dot> on the Range( ) since the .Cells reference the correct sheet (3 and 4)

But if you do, then you do need the dot on the Cells (#5) if you're doing it that way




Option Explicit
Sub ToDotOrNotToDot_ThatIsTheQuestion()

Worksheets("Sheet1").Select
'1 uses active sheet1
MsgBox ActiveSheet.Name & " -- " & Range(Cells(1, 1), Cells(10, 10)).Address(1, 1, 1, 1)


With Worksheets("Sheet2")
'still uses active sheet1
MsgBox ActiveSheet.Name & " -- " & Range(Cells(1, 1), Cells(10, 10)).Address(1, 1, 1, 1)
End With


Worksheets("Sheet2").Select
'2 uses active sheet2
MsgBox ActiveSheet.Name & " -- " & Range(Cells(1, 1), Cells(10, 10)).Address(1, 1, 1, 1)


Worksheets("Sheet2").Select
With Worksheets("Sheet1")
'3 Case: No Dot - uses the 'With ...' sheet
MsgBox ActiveSheet.Name & " -- " & Range(.Cells(1, 1), .Cells(10, 10)).Address(1, 1, 1, 1)
End With


Worksheets("Sheet2").Select
With Worksheets("Sheet1")
'4 Case: With Dot - still uses the 'With ...' sheet
MsgBox ActiveSheet.Name & " -- " & .Range(.Cells(1, 1), .Cells(10, 10)).Address(1, 1, 1, 1)
End With


Worksheets("Sheet2").Select
With Worksheets("Sheet1")
'5 Case: Err 1004 since the dot-less cells are on Sheet2, but the dot-Range has Sheet1 as it's parent
MsgBox ActiveSheet.Name & " -- " & .Range(Cells(1, 1), Cells(10, 10)).Address(1, 1, 1, 1)
End With
End Sub