Pivot Table Refresh is disabled for a PT created by VBA

[plumcloth]

I picked up a really useful VBA script in this forum to create a Pivot table from data in multiple worksheets.
But once the Pivot Table is created, the Refresh button on the Pivot Table, Options tab is disabled or greyed out.
If I click the Refresh dropdown I can see that "Refresh" is greyed out, but "Refresh All" is not - it doesn't refresh my PT.
I amended the code only slightly to pull data only from worksheets whose name begins with "Year"
Would appreciate any pointers - thanks,
PD

Here's the code ...

Sub test()

  Dim i As Long
  Dim arSQL() As String
  Dim objPivotCache As PivotCache
  Dim objRS As Object
  Dim wbkNew As Workbook
  Dim wks As Worksheet


  With ActiveWorkbook
    ReDim arSQL(1 To 6)
    For Each wks In .Worksheets
      If (Left(wks.Name, 4) = "Year") Then
        i = i + 1
        arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
      End If
    Next wks
    Set wks = Nothing
    Set objRS = CreateObject("ADODB.Recordset")


    objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
        .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
  End With


  Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
  Set objPivotCache.Recordset = objRS
  Set objRS = Nothing


  With ActiveWorkbook.Worksheets("Pivot")
      objPivotCache.CreatePivotTable TableDestination:=.Range("A3")
      Set objPivotCache = Nothing
  End With
  
  
End Sub