Excel - disconnected user and invalid filter method

So, I got the <apparently infamous>  'object invoked has disconnected from its clients' error.  (The specific code is -2147417848 (80010108).  And yes, I've read the microsoft article on it. (MS Article Q319832).

Since it returns the error the 2nd time (every 2nd time) the macro is run, I think the article applies.  I've had to read it about 4 or 5 times to understand it (and I think I'm still fuzzy on it a bit), but changing the code to meet the global requirements mentioned still does not seem to correct it.  It always hangs in the same spot (on a filter), so I've tried to just work around it by changing the filter code, to no avail as well.  I still think that might be the answer -- so I'm posting my filter code here and see if I can get some other ideas of ways to apply the filter to get it to work.  

For a little back history...this worksheet was originally created in 2003 with VB6 and was converted last year to Excel 2010.  It just started returning the error about 2 weeks ago.  It breaks on everyone's PC here in the office that runs it, so it is not isolated to one PC.  It also breaks on PC with and without administrative rights to the PC.

I rewrote a good deal of the body of the code.  Rebuilt the pivot tables in the 2010 format.  I've changed the filter code several times, which is where the code breaks each time.  In this example, it breaks on the 2nd line:  
With oXL.Worksheets("Detail Actual vs Plan").Range("1:1")
        .AutoFilter Field:=10, Criteria1:="=1"
    End With

Open in new window

 In this example of code the whole line breaks.    .  

I've attached the full section of code so you can see what it looks like.

I am also going down the rabbit hole of looking at the possibility of the windows update agent causing the problem, but from what I can tell my WUA is up to date.  

Anything else I can check??  I would appreciate any feedback.
Public Sub ResetFilters()
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
'Dim oXL As Excel.Application
Dim oXL As Object

Set oXL = GetObject(, "excel.application")


If Application.Caller = "Button 49" Then
   Var1 = "General Dynamics"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 51" Then
   Var1 = "DIR"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 52" Then
   Var1 = "DELL"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 53" Then
   Var1 = "IBM"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 54" Then
   Var1 = "EDS"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 56" Then
   Var1 = "SCE"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 59" Then
   Var1 = "HP"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 60" Then
   Var1 = "Intel"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 58" Then
   Var1 = "(All)"
   Var2 = "(All)"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 61" Then
   Var1 = "(All)"
   Var2 = "Semple"
   Var3 = "(All)"
Else
If Application.Caller = "Button 62" Then
   Var1 = "(All)"
   Var2 = "Kubenka"
   Var3 = "(All)"
Else
If Application.Caller = "Button 63" Then
   Var1 = "(All)"
   Var2 = "Cardwell"
   Var3 = "(All)"
Else
If Application.Caller = "Button 64" Then
   Var1 = "(All)"
   Var2 = "Tier 1"
   Var3 = "(All)"
Else
If Application.Caller = "Button 65" Then
   Var1 = "(All)"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 66" Then
   Var1 = "(All)"
   Var2 = "Semple"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 67" Then
   Var1 = "(All)"
   Var2 = "Kubenka"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 68" Then
   Var1 = "(All)"
   Var2 = "Cardwell"
   Var3 = "Non-Tier1"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Sheets("Indirect PT").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields("P&L").CurrentPage = Var1
    ActiveSheet.PivotTables("PivotTable3").PivotFields("P&L1").CurrentPage = Var2
    ActiveSheet.PivotTables("PivotTable3").PivotFields("P&L2").CurrentPage = Var3
Sheets("Direct PT").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields("P&L").CurrentPage = Var1
    ActiveSheet.PivotTables("PivotTable7").PivotFields("P&L1").CurrentPage = Var2
    ActiveSheet.PivotTables("PivotTable7").PivotFields("P&L2").CurrentPage = Var3
Sheets("S&M PT").Select
    ActiveSheet.PivotTables("PivotTable8").PivotFields("P&L").CurrentPage = Var1
    ActiveSheet.PivotTables("PivotTable8").PivotFields("P&L1").CurrentPage = Var2
    ActiveSheet.PivotTables("PivotTable8").PivotFields("P&L2").CurrentPage = Var3
Sheets("Corp PT").Select
    ActiveSheet.PivotTables("PivotTable6").PivotFields("P&L").CurrentPage = Var1
    ActiveSheet.PivotTables("PivotTable6").PivotFields("P&L1").CurrentPage = Var2
    ActiveSheet.PivotTables("PivotTable6").PivotFields("P&L2").CurrentPage = Var3
Sheets("Overall PT").Select
    ActiveSheet.PivotTables("PivotTable9").PivotFields("P&L").CurrentPage = Var1
    ActiveSheet.PivotTables("PivotTable9").PivotFields("P&L1").CurrentPage = Var2
    ActiveSheet.PivotTables("PivotTable9").PivotFields("P&L2").CurrentPage = Var3
    
    Worksheets("Detail Actual vs Plan").Activate
    'Sheets("Detail Actual vs Plan").Select
    'Range("J1").Select
    
    
     
    'ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=10
    'ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="=1"
    
    'ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=5
    
    oXL.Worksheets("Detail Actual vs Plan").AutoFilterMode = False
    If Not ActiveSheet.AutoFilterMode Then
    oXL.ActiveSheet.Range("J1").AutoFilter
    End If

    With oXL.Worksheets("Detail Actual vs Plan").Range("1:1")
        .AutoFilter Field:=10, Criteria1:="=1"
    End With
    
    'oXL.ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=5
   'oXL.ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="=1"

    

   Set oXL = Nothing
 

End Sub

Open in new window

Option Explicit

Public Sub Update()
'
' Update Macro
' Macro recorded 6/6/2005
'

'
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("GP Summary").Select
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Overall PT").Select
    Range("A6").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    Sheets("Corp PT").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
    Sheets("Corp PT%").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    Sheets("S&M PT").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
    Sheets("Direct PT").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    Sheets("Direct PT%").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    Sheets("Indirect PT").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    Sheets("Indirect PT%").Select
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    
    Worksheets("Detail Actual vs Plan").Activate
    'Sheets("Detail Actual vs Plan").Select
    Range("J1").Select
    ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=10
    ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="=1"
    
    ActiveSheet.Range("$A$1:$N$139").AutoFilter Field:=5
End Sub

Open in new window

ameekAsked:
Who is Participating?
 
leonstrykerCommented:
If you are coding in Excel, then you do not need Set oXL = GetObject(, "excel.application"). Try this:


Sub test()
    
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String

If Application.Caller = "Button 1" Then
   Var1 = "General Dynamics"
   Var2 = "(All)"
   Var3 = "(All)"
Else
End If
    
    With Worksheets("Indirect PT")
        .Activate
        .PivotTables("PivotTable1").PivotFields("P&L").CurrentPage = Var1
        .PivotTables("PivotTable1").PivotFields("P&L1").CurrentPage = Var2
        .PivotTables("PivotTable1").PivotFields("P&L2").CurrentPage = Var3
    End With

    With Worksheets("Detail Actual vs Plan")
        .Activate
        .Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="1"
    End With
End Sub

Open in new window

0
 
ameekAuthor Commented:
Oh and the references I'm using the code are:

VB for applications
Microsoft Excel 14.0 Object library
OLE Automation
Microsoft Forms 2.0 Object library
Microsoft Office 14.0 Object library
Microsoft ADO ext 6.0 for DDL and Security
Microsoft DAO 3.6 Object library
Microsoft Visual Basic for Applications Extensibility 5.3

I've also tried it with just the following tools and get the same error:
VB for applications
Microsoft Excel 14.0 Object library
OLE Automation
Microsoft Forms 2.0 Object library
Microsoft Office 14.0 Object library
0
 
leonstrykerCommented:
Try it with out the = sign in the Criteria

Criteria1:="1" vs Criteria1:="=1"

Leon



0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
zorvek (Kevin Jones)ConsultantCommented:
No answers but I have some coding suggestions. Instead of that hideous if statement, use Select Case:

Select Case Application.Caller
    Case "Button 49":
        Var1 = "General Dynamics"
        Var2 = "(All)"
        Var3 = "(All)"
    Case "..."
    Case "..."
End Select

One good clue is that this occurs every other time through the code. That is indicative of a toggle property which is common with filtering. Try doubling the statements that turn the auto filter off.

I use this code to turn it off:

    oXL.Worksheets("Detail Actual vs Plan").AutoFilter

versus:

    oXL.Worksheets("Detail Actual vs Plan").AutoFilterMode = False

Kevin
0
 
ameekAuthor Commented:
Hi Leon,

Thanks for the quick feedback.  Tried it... same error.  I guess if I can verify with someone that the code is valid, it might lean more toward the windows dll issue again.  So with the change above you have recommended, does the code look otherwise sound?

Angela
0
 
leonstrykerCommented:
So with the change above you have recommended, does the code look otherwise sound?

No. Try this for the Update code:
Public Sub Update()

    Worksheets("GP Summary").Range("A2").QueryTable.Refresh BackgroundQuery:=False
    Worksheets("Overall PT").PivotTables("PivotTable9").PivotCache.Refresh
    Worksheets("Corp PT").PivotTables("PivotTable6").PivotCache.Refresh
    Worksheets("Corp PT%").PivotTables("PivotTable3").PivotCache.Refresh
    Worksheets("S&M PT").PivotTables("PivotTable8").PivotCache.Refresh
    Worksheets("Direct PT").PivotTables("PivotTable7").PivotCache.Refresh
    Worksheets("Direct PT%").PivotTables("PivotTable2").PivotCache.Refresh
    Worksheets("Indirect PT").PivotTables("PivotTable3").PivotCache.Refresh
    Worksheets("Indirect PT%").PivotTables("PivotTable3").PivotCache.Refresh

    With Worksheets("Detail Actual vs Plan")
        .Activate
        .Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="1"
    End With
End Sub

Open in new window

0
 
ameekAuthor Commented:
Oh...thanks!  Ok so I put that in there... and then for the ResetFilters section I swapped out the filter code with what you provided for another shot ... it still returns the disconnected client, but this time on the 3rd line.

 
With Worksheets("Detail Actual vs Plan")
        .Activate
        .Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="1"
    End With

Open in new window


It never disconnects on that line of code though when the update sub is run multiple times.. .so I keep thinking it can't be the actual filter code itself.

It's also the only sheet that has that problem...and we run a ton of excel macros here.  Which makes me think it's not the dll/PC issue or they would all break.  Is it possible this thing is corrupt since it's so old and just needs to be rebuilt from scratch (I really hope the answer is no though because it's got several dozen worksheets in it...)
0
 
Rory ArchibaldCommented:
Are you running this code in Excel or VB? If it's in VB, then almost all your references to Excel objects are unqualified, which may well be the root of the problem.
0
 
leonstrykerCommented:
Try that piece of code in a blank spread sheet. Just open a new workbook, rename one of the sheets, drop some sample data, and add the code, and run it. Lets see what happens.
0
 
ameekAuthor Commented:
Hi rorya,

It is a VB macro in an Excel worksheet.  If I step through the VB, it works fine every time, even stepping through multiple times.  When I run it from the buttons, that is when it breaks.

leaonstryker,

I rebuilt a mini version of it in a new sheet.  If I use JUST the filter code, it runs fine.  The minute I put one button reference in there, it breaks on the filter code on the second try.

So it is not the filter code itself... which leads us back to the unqualified references.  I've read the Microsoft article multiple times and I must just not get what that means exactly.  I thought qualifying it with the oXL reference would have fixed it (shown below), however it returns an error in the new sheet just as it did in the original code when I tried it at one point.  (It basically says it can't find the pivot table I'm referencing).  

If somenoe would be so kind as to point me in the direction of a better example (or two or three) of fully qualifying references, maybe I can read up on that some more and get my mind wrapped around it better.

Thanks for all the feedback!!

Angela
Sub test()
    
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
'Dim oXL As Excel.Application
Dim oXL As Object

Set oXL = GetObject(, "excel.application")


If Application.Caller = "Button 1" Then
   Var1 = "General Dynamics"
   Var2 = "(All)"
   Var3 = "(All)"
Else
End If

Sheets("Indirect PT").Select
    oXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("P&L").CurrentPage = Var1
    oXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("P&L1").CurrentPage = Var2
    oXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("P&L2").CurrentPage = Var3
    
    With oXL.Worksheets("Detail Actual vs Plan")
        .Activate
        .Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="1"
    End With
   
   Set oXL = Nothing
 
End Sub

Open in new window

0
 
ameekAuthor Commented:
Wait... brain fart alert.  

So to fully qualify it should be Worksheets("Indirect PT").PivotTables("PivotTable1").PivotFields("P&L").CurrentPage ..blah blah blah... instead of

Sheets("Indirect PT").Select
     ActiveSheet.PivotTables("PivotTable1").PivotFields("P&L").CurrentPage = Var1
0
 
ameekAuthor Commented:
Ok...no.  Still sitting in the stupid corner over here.  I tried the code below (it's in a brand new, created from scratch xlsm spreadsheet btw) and it still breaks the 2nd time out so I guess I don't understand fully qualified yet.


Sub test()
    
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String

Dim oXL As Object

Set oXL = GetObject(, "excel.application")

If oXL.Application.Caller = "Button 1" Then
   Var1 = "General Dynamics"
   Var2 = "(All)"
   Var3 = "(All)"
Else
End If
    
    With Worksheets("Indirect PT")
        .Activate
        .PivotTables("PivotTable1").PivotFields("P&L").CurrentPage = Var1
        .PivotTables("PivotTable1").PivotFields("P&L1").CurrentPage = Var2
        .PivotTables("PivotTable1").PivotFields("P&L2").CurrentPage = Var3
    End With

    With Worksheets("Detail Actual vs Plan")
        .Activate
        .Range("$A$1:$N$139").AutoFilter Field:=10, Criteria1:="1"
    End With
    
Set oXL = Nothing
    
End Sub

Open in new window

0
 
ameekAuthor Commented:
I tried your cleaner code leonstryker (without the oXL qualifier...which I am glad to know is not required since it didn't seem to make sense to me to have to qualify an application within itself...), with the same error results.  

I keep trying to find the common element that causes the error and I can't come up with it.

The filter code works by itself (which would imply it's not an issue with that code)
The other code works by itself (which would imply it's not an issue with that code)
It only breaks on this code in this sheet...it's not breaking on our other spreadsheets (which would imply it's a problem with the sheet or the PC
It consistently breaks when put in a clean sheet, which would imply it's not a problem with the sheet itself
It consistently breaks on everyone's PC, which would indicate it's not a problem with the PC... unless a global update was applied to all PCs to cause the same problem (but then that brings me right back to why wouldn't it break every spreadsheet we use

The only time it breaks is when you put these two pieces of code together; however the coding format itself has changed several times so it shouldn't be the code itself...but more likely the end result/actions being peformed together.  But why in the world would you not be able to click a button to reset a variable and then reset a filter?  
0
 
Rory ArchibaldCommented:
Can you post a demo workbook?
0
 
leonstrykerCommented:
What is the data source for "PivotTable1"? Is it Range("$A$1:$N$139")? Are you trying to have a Pivot Table on a filtered range?
0
 
ameekAuthor Commented:
PivotTable1 source is just a dump of query data from another spreadsheet.  The pivot1 is built over this data and has certain fiters applied to it (in the real world copy, there are about 6 or so pivots on top of this same data, each with their own filter).

There is a another spreadsheet (Detail vs Actual Plan) that pulls totals (not with a pivot, just with vlookups) from the other pivot tables.  It is this spreadsheet that then applies the filter that is breaking.

I've attached a demo workbook that doesn't have the vlookups in it on the "Detail" tab that the working copy does, but that doesn't keep the filter from breaking at all.  The filter is simply the number "1' on a column.

I guess if someone would be so kind as to click on button 1 a couple of times first without messing with the code.  If it doesn't break, then that would narrow it down to either missing references or a PC update problem.  Once you open up the code and start working with it, you usually have to save it, close it, and then reopen it to get the error to occur again.
testing.xlsm
0
 
Rory ArchibaldCommented:
Works fine for me - clicked about 10 times without issue.
0
 
ameekAuthor Commented:
rorya, thanks!  One more question -- did it keep the same references in yours?  (VB for Apps, MS Excel 14.0 Object Library, OLE Automation, and MS Office 14.0 Object library)
0
 
Rory ArchibaldCommented:
Yes - references are workbook specific, and those are the default ones. I will try and test on my work installation of 2010 shortly, as my hone one had the SP1 beta of office installed.
0
 
ameekAuthor Commented:
Yeah, I tested mine on my home install last night and it breaks at home.  I'm running Windows 7/Office 2010 home version there; at work I'm running Windows 7/Office 2010 professional.  
0
 
Rory ArchibaldCommented:
Tested at work (Office 2010 on Win XP) and it's fine here too. (I do also have 2003 and 2007 installed)
The only thing I did notice is that the second run seems to take longer than all the others for some reason, which may be related.
0
 
ameekAuthor Commented:
Ok..I'm going to get a few test environments set up on some laptops here (win xp with 2010) and win 7 with 2010, but with the office/excel 10.0 tools and do some testing to see what falls out.
0
 
ameekAuthor Commented:
So our testing here still errored out for us on Win xp / office 2010.  IT played around with it and what they came up with for a fix was:

With Worksheets("Detail Actual vs Plan")
        .Range("$J1").AutoFilter Field:=10, Criteria1:="1"
        .Activate
    End With

For whatever reason, putting the .Activate before the filter line would cause it to break in our environment, but putting it after the filter field it seems to work fine.  I've added the final code that I went with if anyone wants to see it.

I'm giving you both points for sticking with me and updating my code.  Thanks so much for your help!


Option Explicit

Public Sub Update()
'
' Update Macro
' Macro recorded 6/6/2005 by
'
   
   Worksheets("GP Summary").Range("A2").QueryTable.Refresh BackgroundQuery:=False
    Worksheets("Overall PT").PivotTables("PivotTable9").PivotCache.Refresh
    Worksheets("Corp PT").PivotTables("PivotTable6").PivotCache.Refresh
    Worksheets("Corp PT%").PivotTables("PivotTable3").PivotCache.Refresh
    Worksheets("S&M PT").PivotTables("PivotTable8").PivotCache.Refresh
    Worksheets("Direct PT").PivotTables("PivotTable7").PivotCache.Refresh
    Worksheets("Direct PT%").PivotTables("PivotTable2").PivotCache.Refresh
    Worksheets("Indirect PT").PivotTables("PivotTable3").PivotCache.Refresh
    Worksheets("Indirect PT%").PivotTables("PivotTable3").PivotCache.Refresh
   
End Sub

Public Sub ResetFilters()
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String

If Application.Caller = "Button 49" Then
   Var1 = "General Dynamics"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 51" Then
   Var1 = "DIR"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 52" Then
   Var1 = "DELL"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 53" Then
   Var1 = "IBM"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 54" Then
   Var1 = "EDS"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 56" Then
   Var1 = "SCE"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 59" Then
   Var1 = "HP"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 60" Then
   Var1 = "Intel"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 58" Then
   Var1 = "(All)"
   Var2 = "(All)"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 61" Then
   Var1 = "(All)"
   Var2 = "Semple"
   Var3 = "(All)"
Else
If Application.Caller = "Button 62" Then
   Var1 = "(All)"
   Var2 = "Kubenka"
   Var3 = "(All)"
Else
If Application.Caller = "Button 63" Then
   Var1 = "(All)"
   Var2 = "Cardwell"
   Var3 = "(All)"
Else
If Application.Caller = "Button 64" Then
   Var1 = "(All)"
   Var2 = "Tier 1"
   Var3 = "(All)"
Else
If Application.Caller = "Button 65" Then
   Var1 = "(All)"
   Var2 = "(All)"
   Var3 = "(All)"
Else
If Application.Caller = "Button 66" Then
   Var1 = "(All)"
   Var2 = "Semple"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 67" Then
   Var1 = "(All)"
   Var2 = "Kubenka"
   Var3 = "Non-Tier1"
Else
If Application.Caller = "Button 68" Then
   Var1 = "(All)"
   Var2 = "Cardwell"
   Var3 = "Non-Tier1"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

With Worksheets("Indirect PT")
    .Activate
    .PivotTables("PivotTable3").PivotFields("P&L").CurrentPage = Var1
    .PivotTables("PivotTable3").PivotFields("P&L1").CurrentPage = Var2
    .PivotTables("PivotTable3").PivotFields("P&L2").CurrentPage = Var3
End With
With Worksheets("Direct PT")
    .Activate
    .PivotTables("PivotTable7").PivotFields("P&L").CurrentPage = Var1
    .PivotTables("PivotTable7").PivotFields("P&L1").CurrentPage = Var2
    .PivotTables("PivotTable7").PivotFields("P&L2").CurrentPage = Var3
End With
With Worksheets("S&M PT")
    .Activate
    .PivotTables("PivotTable8").PivotFields("P&L").CurrentPage = Var1
    .PivotTables("PivotTable8").PivotFields("P&L1").CurrentPage = Var2
    .PivotTables("PivotTable8").PivotFields("P&L2").CurrentPage = Var3
End With
With Worksheets("Corp PT")
    .Activate
    .PivotTables("PivotTable6").PivotFields("P&L").CurrentPage = Var1
    .PivotTables("PivotTable6").PivotFields("P&L1").CurrentPage = Var2
    .PivotTables("PivotTable6").PivotFields("P&L2").CurrentPage = Var3
End With
With Worksheets("Overall PT")
    .Activate
    .PivotTables("PivotTable9").PivotFields("P&L").CurrentPage = Var1
    .PivotTables("PivotTable9").PivotFields("P&L1").CurrentPage = Var2
    .PivotTables("PivotTable9").PivotFields("P&L2").CurrentPage = Var3
End With
    
With Worksheets("Detail Actual vs Plan")
    .Range("$J1").AutoFilter Field:=10, Criteria1:="1"
    .Activate
End With


End Sub

Open in new window

0
 
ameekAuthor Commented:
You gave me excellent tips on coding, looking at unqualified code, and took time to test the app for me.  I really appreciate your feedback.  

If you know why the .Activate line would go after the filter to fix this problem and care to post the reason...that would be nice to know at least ... to be able to make sense of it all!  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.