Updating PivotTable Sourcedata from Worksheet

I am a novice in VBA (know just enough to be dangerous) and new to this forum - hoping someone can help me out here...:wave:

I am trying to write a macro that will take data from a worksheet (named SQLDoc) and update the SourceData property of a PivotTable. I am doing this b/c I have a number of files (100+) with PivotTables that need a slight tweak to the queries they are using (all of the queries are fairly long-running, and MS Query takes _forever_ to run them :( ).

I can't seem to be able to update the SourceData property in my PivotTable. I tried to ReDim it to a single array element (and then would ReDim it to add another element for each additional query line), but I got errors doing that. I am trying to simply overwrite what's in the array, but my changes don't seem to take hold. I can't figure out quite what I'm doing wrong here...:eek2:

The data in the SQLDoc tab contains 3 columns (TabName, PTName, and Query) in the format:

-----------------------------------------------------------------------------------------------------------------------------------------
| Tab1 | PivotTable1 |
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | DSN=data_cube;Description=data_cube;APP=Microsoft Office 2003;DATABASE=Data_CUBE;Trusted_Connection=Yes
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | SELECT InvoiceData.FullYear, InvoiceData.Quarter, InvoiceData.InvoicePeriod, DW_SalesRegionTerritory.SalesRegion ,
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | {...}
-----------------------------------------------------------------------------------------------------------------------------------------
| Tab2 | PivotTable1 |
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | DSN=data_cube;Description=data_cube;APP=Microsoft Office 2003;DATABASE=Data_CUBE;Trusted_Connection=Yes
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | SELECT 'YTD' AS 'Scope', InvoiceData.FullYear, InvoiceData.InvoicePeriod, '2006' AS 'CurrentYear',
-----------------------------------------------------------------------------------------------------------------------------------------
| xxxx | xxxxxxxxxxx | {...}
-----------------------------------------------------------------------------------------------------------------------------------------

Code:

---

intTabNameColumn = 1
intPTNameColumn = 2
intSQLColumn = 3
intSQLRow = 2
strSheetName = "SQLDoc"

With Worksheets(strSheetName)
    strTabName = .Cells(intSQLRow, intTabNameColumn).Value
    strPTName = .Cells(intSQLRow, intPTNameColumn).Value
End With
   
For Each ws In Worksheets
    If StrComp(ws.Name, strTabName, vbTextCompare) = 0 Then
        For Each pt In ws.PivotTables
            If StrComp(pt.Name, strPTName, vbTextCompare) = 0 Then
                intSQLRow = intSQLRow + 1
                x = 1
               
                Do While Worksheets(strSheetName).Cells(intSQLRow, intSQLColumn).Value <> ""
                    strSQL = Worksheets(strSheetName).Cells(intSQLRow, intSQLColumn).Value
                    'ReDim Preserve pt.SourceData(x)
                       
                    pt.SourceData(x) = strSQL
                       
                    x = x + 1
                    intSQLRow = intSQLRow + 1
                Loop
            End If
        Next pt
    End If
Next ws

---