Refresh pivot for some sheets fails-CodePudding

I want to refresh some pivots in excel which has many pivots in the workbook. Say sheet A & B has pivots and want to refresh the same -

Pls let me knwo if the below is correct - I am getting an error "Invalid use of property"

Sub Refreshsomepivot()

    Dim vSheet As Worksheet
    Dim vSheets As Worksheets
    Dim pt As PivotTable
    vSheets = Array("A, B")
    For Each vSheet In vSheets
        MsgBox (vSheet.name)
            For Each pt In Sheets(vSheet).PivotTables
                pt.RefreshTable
            Next pt
    Next vSheet
End Sub

CodePudding user response：

You are mixing Worksheets and Worksheet names. Your Array is meant to be an array of worksheet names, not an array of worksheets (Worksheets, btw, is a Collection, not an array).
Furthermore, your array is not an array of 2 elements, it's has only one element as your comma in withing the string - it needs to be Array("A", "B")

On more point you need to know is that Array returns an array of Variant, not String, therefore you need to declare it as such. If you loop over such an array, the "running" variable needs be be declared also as Variant.

Have a look to the following code:

Dim vSheetNames() As Variant, vSheetName As Variant

vSheetNames = Array("A", "B")
For Each vSheetName In vSheetNames
    Dim vSheet As Worksheet
    Set vSheet = ThisWorkbook.Sheets(vSheetName)
    MsgBox vSheet.Name
    Dim pt As PivotTable
    For Each pt In vSheet.PivotTables
        pt.RefreshTable
    Next
Next