CODEWITHSUNDEEP
vbaexcel
jhovyn
jhovyn

Reputation: 265

VBA - Method Activate of Worksheet Failed?

What is wrong with my code below?

I got an error

Method Activate of Worksheet Failed

Sub addMOnth()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate    
    If ws.Name <> "SUMMARY" And ws.Name <> "Store" And ws.Name <> "Apps" Then

        ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Report Date Structure].[Month].[Month]").VisibleItemsList = Array( _
        "[Report Date Structure].[Month].&[2.01711E5]")        
    End If
Next ws

End Sub

Upvotes: 0

Views: 786

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

There is no need to Activate the ws just to filter the PivotTable.

Try the code below, explanation inside the code's comments:

Sub addMOnth()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        Select Case .Name
            Case "SUMMARY", "Store", "Apps"
                ' do nothing

            Case Else ' equivalent to your If with multiple AND
                .PivotTables("PivotTable1").PivotFields( _
                    "[Report Date Structure].[Month].[Month]").VisibleItemsList = Array( _
                    "[Report Date Structure].[Month].&[2.01711E5]")
        End Select            
    End With
Next ws

End Sub

Upvotes: 1

Related Questions