• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

'Run-time error '91': Obj. variable or With...not set' - for ActiveWorkbook.Worsheets line (VBA)

K

KC E

Member
Hello,

I am using Excel 2013 and have used the macro recorder to apply a custom sort on column D first then column C. This code is straight from the macro recorder. I added the Dim statements, Set wb=ActiveWorkbook, and wkshtname = Activesheet.Name lines.

The original macro code had the worksheet name wherever there is code like this below, but I tried to replace the actual worksheet name with the variable 'wkshtname'.

Code: 
ActiveWorkbook.Worksheets("Actual Worksheet Name"), etc.

I get the above 'Run-time error '91': Obj. variable or With block variable not set' for this line of code in the full code below. I have activeworkbook open and there are no other workbooks open.

Code: 
ActiveWorkbook.Worksheets(wkshtname).AutoFilter.Sort.SortFields.Clear

I've looked at many, many websites and have tried to fix this:

  1. I tried 'Set wkshtname = = ActiveSheet.Name' but I get an error that 'Set' is not correct.
  2. I added 'Dim wb, etc.', but I don't see why I need to 'Dim wb, etc.' b/c I don't use the variable. I just didn't know if Excel was not recognizing the ActiveWorkbook part of the line.
  3. I tried using the Worksheet number index like below but that didn't work either:

Code: 
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Clear

Can anyone help to resolve this? I've included a test file below. The code is in Module 1. I appreciate the help.

Code: 
Sub macrosort()

Dim wb As Workbook
Dim wkshtname As String
Dim lnglastrow as Long


Set wb = ActiveWorkbook
wkshtname = ActiveSheet.Name
lnglastrow = ActiveSheet.UsedRange.Rows.Count

'sort by Hub column D and then by Week column C before run color code below so will color correctly
ActiveWorkbook.Worksheets(wkshtname).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(wkshtname).AutoFilter.Sort. _
        SortFields.Add Key:=Range("D2:D" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(wkshtname).AutoFilter.Sort. _
        SortFields.Add Key:=Range("C2:C" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(wkshtname).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
 

Attachments

Hello n Welcome to chandoo forum. KC E

Thanks for positing question so in detail but Sorry could not read your complete question..

Tried to understand with the code what expected to be done..Hope below attatched is as per your requirment.

Any questions...Let us know..Happy to help you.
 

Attachments

Last edited:
Hi !

Lines from original code :
Code: 
wkshtname = ActiveSheet.Name

ActiveWorkbook.Worksheets(wkshtname)
This two lines are good on syntax side but are redundant and useless
'cause second codeline means only ActiveSheet ‼
It is just a waste of ressources and execution time !

If you need to work with the active worksheet,
so just use ActiveSheet object statement, nothing else …
​
 
Also another reason you get the error. Is that you are trying to sort autofilter when there isn't any autofilter applied to the sheet.

Something like below should work.
Code: 
Sub macrosort()

Dim lnglstRow As Long

lnglstRow = ActiveSheet.UsedRange.Rows.Count

'sort by Hub column d and then by Week column c before run color code below so will color correctly

With ActiveSheet
' Check if the sheet has autofilter, if yes, clear sort fields. Otherwise, apply autofilter
    If .AutoFilterMode = True Then
        .AutoFilter.Sort.SortFields.Clear
    Else
        .Range("A1").AutoFilter
    End If
    .AutoFilter.Sort. _
        SortFields.Add Key:=Range("d2:d" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    .AutoFilter.Sort. _
        SortFields.Add Key:=Range("c2:c" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
End With

With ActiveSheet.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub
 
Monty said:
Hello n Welcome to chandoo forum. KC E

Thanks for positing question so in detail but Sorry could not read your complete question..

Tried to understand with the code what expected to be done..Hope below attatched is as per your requirment.

Any questions...Let us know..Happy to help you.
Click to expand...

Monty, Thank you! It worked, and it is a much more streamlined code than what I had. I was surprised b/c it looked like it would sort c first, then d, but it didn't. This is like the sort where you sort one column at a time and have to do it in reverse vs. the custom sort with the dialog box where you can add a level, correct?

I will ask Chihiro this, as well, since they brought it up, but it didn't seem to have problem with my variable, 'wkshtname', but it seems, like Chihiro says, that the autofilter was not turned on, I guess? I just thought those kind of errors were b/c a person doesn't use Set x = y or x=y in the beginning of the code.

Thank you, again!!
 
Chihiro said:
Also another reason you get the error. Is that you are trying to sort autofilter when there isn't any autofilter applied to the sheet.

Something like below should work.
Code: 
Sub macrosort()

Dim lnglstRow As Long

lnglstRow = ActiveSheet.UsedRange.Rows.Count

'sort by Hub column d and then by Week column c before run color code below so will color correctly

With ActiveSheet
' Check if the sheet has autofilter, if yes, clear sort fields. Otherwise, apply autofilter
    If .AutoFilterMode = True Then
        .AutoFilter.Sort.SortFields.Clear
    Else
        .Range("A1").AutoFilter
    End If
    .AutoFilter.Sort. _
        SortFields.Add Key:=Range("d2:d" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    .AutoFilter.Sort. _
        SortFields.Add Key:=Range("c2:c" & lnglstRow), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
End With

With ActiveSheet.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub
Click to expand...

Thank you, Chihiro. Your code worked, as well. I was asking Monty, but I want to ask you b/c you mentioned it: So it didn't have problem with my variable, 'wkshtname', but it seems, like Chihiro says, that the autofilter was not turned on, I guess? I just thought those kind of errors were b/c a person doesn't use Set x = y or x=y in the beginning of the code.

Thank you for all of your help.
 
Marc L said:
Hi !

Lines from original code :
Code: 
wkshtname = ActiveSheet.Name

ActiveWorkbook.Worksheets(wkshtname)
This two lines are good on syntax side but are redundant and useless
'cause second codeline means only ActiveSheet ‼
It is just a waste of ressources and execution time !

If you need to work with the active worksheet,
so just use ActiveSheet object statement, nothing else …
​
Click to expand...

Thank you, Marc L. I appreciate your help. I am trying to learn Excel VBA and I appreciate your help on streamlining the code. Thanks.
 
You must log in or register to reply here.
Back
Top