(Solved) – Bringing in SQL data and creating lots of PivotTables – looking for a way to eliminate the need to reference each pivottable in the code


Background: I have a workbook with a lot of tabs that’s brings in data from SQL database (please see example query below). And due to a lot of reasons, I need to create many pivot tables in each tab because each pivot table needs to look at one particular set of data. Every-time I create a pivot table I have to go to “Pivot Table Options” and jot down the pivottable number and then update the VBA code with the appropriate sequential code Set pvt = Me.PivotTables("PivotTable###").

Questions: Is there some VBA code that can reference all the pivot tables within each tab without the need to reference a pivotable number? Cause I have a lot of tabs and I need to create a lot of pivot tables and I’m losing track of the pivotable numbers.

Sub RefreshData()
    Dim SQl As String
    Dim startDATE As String, endDATE As String
    Dim pvt As PivotTable

    Set pvt = Me.PivotTables("PivotTable1")
    Set pvt = Me.PivotTables("PivotTable2")
    Set pvt = Me.PivotTables("PivotTable3")

    'pvt.PivotCache.Connection = "ODBC;DSN=" & Application.Range("DSN_Source") & ";DATABASE=" & Application.Range("T123") & ";"

    startDATE = Format(Me.Range("SDate"), "yyyy-mm-dd hh:mm:ss")
    endDATE = Format(Me.Range("EDate"), "yyyy-mm-dd hh:mm:ss")

    SQl = ""

    SQl = "WITH ABC As " & vbCrLf
    SQl = SQl & "( " & vbCrLf
    SQl = SQl & "  SELECT Stuff  " & vbCrLf
    SQl = SQl & ", more stuff,  ROW_NUMBER() OVER (PARTITION BY RIGHT(Stuff, 2) " & vbCrLf
    SQl = SQl & ", stuff1, stuff2, stuff3 ORDER BY Estuff4 DESC) As RowNum" & vbCrLf

    SQl = SQl & " FROM StuffTable " & vbCrLf

    SQl = SQl & " WHERE stuff = 'stuffy'  AND " & vbCrLf


    With pvt.PivotCache
        .CommandType = xlCmdSql
        .CommandText = SQl
    End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *