Pivot Table from database query

[shaunk]

I am attempting to create a pivot table based on data from a worksheet created by a database query.

Psuedo code is quite simple:

Call the subroutine to build the querytable in sheet3
Execute the code to build the pivottable based on data in sheet3.

Each time I execute the code to build the pivottable after calling the subroutine to build the querytable, it fails with "incorrect pivot table name".
However, if I execute the code without calling the querytable subroutine (i.e working off static data already created by a previous run of the querytable subroutine), it works as expected.

Here is the code:

Public Reporting_Unit

Sub Pivot_Acat(Optional Reporting_Unit)

    Dim Calling_Unit As String
        
    Dim MyDest As String, MyRange As String, MyPivotTable As String
    
    'Calling_Unit = Application.Caller
    Calling_Unit = "ACAT"
    Call Access_DB(Calling_Unit)
    MyDest1 = "Sheet4"
    MyRange1 = "A1"
    MyPivotTable1 = "BOB"
    Sheets(MyDest1).Range("A1:Z30").Delete
    MySource1 = "Sheet3!R1C1:R17C25"
    Worksheets("Sheet4").Activate

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=MySource1).CreatePivotTable _
    TableDestination:=Sheets(MyDest1).Range(MyRange1), TableName:=MyPivotTable1
    
    Worksheets(MyDest1).Activate
    
    ActiveSheet.PivotTables(MyPivotTable1).SmallGrid = False
    ActiveSheet.PivotTables(MyPivotTable1).AddFields RowFields:=Array("Org_Name", _
        "Data"), ColumnFields:="Start_Range"

    With ActiveSheet.PivotTables(MyPivotTable1).PivotFields("New_Referrals")
        .Orientation = xlDataField
        .Caption = "Refs"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables(MyPivotTable1).PivotFields("New_Clients_Seen")
        .Orientation = xlDataField
        .Caption = "Seen"
        .Function = xlSum
    End With
    
End Sub

Sub Access_DB(Reporting_Unit)
' Declare the QueryTable object

Dim Cutie As QueryTable

Sheets("Sheet3").Range("A:A:Z:Z").Delete

' Set up the SQL Statement
sqlstring = "select * from KPI_Auto_Data where Reporting_Unit = " & "'" & Reporting_Unit & "'"
MsgBox "SQL " & sqlstring
'sqlstring = "select * from KPI_Auto_Data "

' Set up the connection string, reference an ODBC connection
' There are several ways to do this

connstring = _
 "ODBC;DSN=KPI_Summary_Data;UID=;PWD=;Database=KPI.mdb"

' Set the active worksheet
'Worksheets("Sheet3").Activate

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
Set Cutie = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
    
Cutie.Refresh

End Sub

Any ideas are much appreciated.

 

[shaunk]

I solved this problem by copying the data in sheet3 created by the querytable subroutine, to another worksheet (sheet4) as "values only".
I then used sheet4 as the datasource to the pivottable.
And it works...hooray.

I think the problem was using the querytable object as the datasource?