My first post on the forum so hello to everybody. This forum has been a great resource to me over the last few weeks as I've needed to put together a VBA project and your advice has helped me to put my ideas into actual code! I am having a bit of a problem though, which I am hoping someone can help me with.

I have a number of excel files which each contain sheets with large tables that use Autofilters. I use a "master" workbook with VBA code to then run macros on these other excel files. Before doing anything on these files, I need to clear any Autofilters that people may have on (clear the filters not remove them). At the moment I am using the ShowAllData method in a function which I call, as shown in the code below...

Private Function RemoveFilters(Ws As Worksheet)
    If Ws.AutoFilterMode Then
        If Ws.FilterMode Then
            Ws.ShowAllData
        End If
    End If
End Function
And the call to the function...

Call RemoveFilters(MWb.Sheets(sheetName))
Now this fails at the "Ws.ShowAllData" line, saying ShowAllData Method of Worksheet class failed. However, if I make the worksheet the active worksheet, it works! Quite frustrating, and I can't see why it doesn't work when the sheet is inactive. I found another thread (link below), where the issue is partially discussed and it's suggested it should work but it never gets resolved.

http://www.excelforum.com/excel-prog...sheet-vba.html

Any help is much appreciated, and I am a VBA novice so please be gentle!

Chris