+ Reply to Thread
Results 1 to 4 of 4

Method autofiltermode of worksheet failed with activewindow.selectedsheets

  1. #1
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    273

    Method autofiltermode of worksheet failed with activewindow.selectedsheets

    Hello,

    I'm trying to select multiple sheets based on their color and perform other tasks on each one. I need to make sure the autofilter mode is off on these sheets, the codes I have now don't work:

    PHP Code: 
    Sub removeFilterRecBook()

    Dim sh As Worksheet
    Dim wsNames
    () As String
      Dim wsColor
    () As Integer
      Dim ws 
    As Worksheet
      Dim ind 
    As Integer
      Workbooks
    ("Recs.xlsm").Activate
      
    If ActiveWindow.SelectedSheets.Count 1 Then
      Worksheets
    ("111100Detail").Select
      
    Else
    Worksheets("111100Detail").Select
      End 
    If
      
    ReDim wsNames(0)
      
    ReDim wsColor(0)
      
    wsNames(0) = ActiveSheet.Name
      wsColor
    (0) = ActiveSheet.Tab.ColorIndex
      
      
    For Each ws In ActiveWorkbook.Sheets
        
    If ws.Tab.ColorIndex wsColor(0Then
          ReDim Preserve wsNames
    (UBound(wsNames) + 1)
          
    ReDim Preserve wsColor(UBound(wsColor) + 1)
          
    wsNames(UBound(wsNames)) = ws.Name
          wsColor
    (UBound(wsColor)) = ws.Tab.ColorIndex
        End 
    If
      
    Next ws

      Sheets
    (wsNames).Select
    For Each sh In ActiveWindow.SelectedSheets
    If sh.AutoFilterMode Then
    sh
    .AutoFilterMode False
    End 
    If
    Next sh

    If ActiveWindow.SelectedSheets.Count 1 Then
      Worksheets
    ("TB").Select
      
    Else
    Worksheets("TB").Select
    End 
    If
    On Error GoTo 0
    End Sub 
    I got an error on the line "sh.autofiltermode = false". Can someone please help me to fix this?

    thanks so much

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Method autofiltermode of worksheet failed with activewindow.selectedsheets

    Hi there,

    I think the problem is caused by trying to remove the AutoFilter from a group of selected worksheets, so see if the following code does what you need:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    273

    Re: Method autofiltermode of worksheet failed with activewindow.selectedsheets

    Hello Greg, it works like a charm! thank you so much! Not only did you provide me with the codes but also advised on where I did wrong...YOU ARE AWESOME!!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Method autofiltermode of worksheet failed with activewindow.selectedsheets

    Hi again,

    Many thanks for your feedback and also for your kind words.

    You're welcome - glad I was able to help.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Add footer to all sheets in ActiveWindow.SelectedSheets except for first sheet
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-12-2019, 09:58 AM
  2. Move worksheet method failed
    By tamzgha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2014, 08:19 AM
  3. PasteSpecial Method of Worksheet class failed
    By Gitch28 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2012, 08:54 PM
  4. trouble with ActiveWindow.SelectedSheets.PrintPreview
    By ArielZusya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-23-2010, 12:59 PM
  5. paste method of worksheet failed
    By Theodjinn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-26-2008, 07:36 AM
  6. [SOLVED] Paste method of worksheet class failed
    By Maxi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2006, 11:40 AM
  7. ActiveWindow.SelectedSheets.PrintOut
    By excel-lent in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-15-2005, 10:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1