Sorry, i stopped reading here :
| The problem is the Pivot Table Datasource is in R1C1 addressing
| Can anybody figure out how in VBA to refernce R1C1 address


To create the pivottable, use a name
(insertion / name ...) with this kind of formula as example
=Bd!$A$1:DECALER(Bd!$F$1;0;0;NBVAL(Bd!$A:$A))

And to update your pivottable each time you update your querytable
You may use a class module....

Class module name : AppQt

Class module Code :
Public WithEvents AppQt As QueryTable
'---------------------------------
Private Sub AppQt_AfterRefresh(ByVal Success As Boolean)
If Success = True Then
Worksheets("Bd").PivotTables("Denis").PivotCache.R efresh
Else
MsgBox "Query failed or was cancelled"
End If
End Sub
'---------------------------------

In a general module, This code :

Dim AppObject As New AppQt
'----------------------------------
Sub Init()
Set AppObject.AppQt = ThisWorkbook.Worksheets("Feuil1").QueryTables(1)
End Sub
'----------------------------------

And in the ThisWorkbook :
'---------------------------
Private Sub Workbook_Open()
Module1.Init
End Sub
'---------------------------




"Joel" a écrit dans le message de groupe de discussion :
...
I know how to create a pivot table. the problem is with the update the 2nd
time the query table is refreshed. the pivot table is not synchronized with
the query.

The real problem is how do you define a range in VBA when you have a R1C1
address range.



"MichDenis" wrote:

Hi Joel,

Try this :

'-----------------------------------
Sub PivotTable()

Dim Adr As String 'Source
Dim Adr1 As String 'Destination
Dim PT As PivotTable

'Where will be the pivottable
With Worksheets("Feuil2")
Adr1 = .Name & "!" & .Range("G10").Address
'Where are the data
Adr = .Name & "!" & .Range("A1:B" & _
.Range("B65536").End(xlUp).Row).Address
End With
'creation of the PivotTable
Set PT = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, SourceData:=Range(Adr)) _
.CreatePivotTable(TableDestination:=Range(Adr1), _
TableName:="MyPivotTable", DefaultVersion:=xlPivotTableVersion10)
With PT
.AddFields RowFields:="Field"
.PivotFields("field").Orientation = xlDataField
End With
End Sub
'-----------------------------------


"Joel" a écrit dans le message de groupe de discussion
:
...
I had an interesting problem today. A posting had a SQL Query in a macro
then wanted to create a Pivot table. The user also want to be able to
refresh the query and update the pivot table.

Since the number of the rows of the query can change the datasource of the
pivot table would also change; the datasource of the query table also needs
to be changed after the query was updated.

The problem is the Pivot Table Datasource is in R1C1 addressing

Can anybody figure out how in VBA to refernce R1C1 address

set MyRange = Range("Sheet2!R1C1:R6C4") doesn't work

neither does
set Myrange = Evaluate("indirect(Sheet2!R1C1:R6C4,false)")

I ended up doing the following

MyRange = "Sheet2!R1C1:R6C4"
'Remove Sheet Name
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") - 1)
'remove sheet and first R from R1C1 address
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") + 2)
FirstRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
FirstCol = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"R") + 1)
LastRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
LastCol = Val(MyRange)

with Sheets(MySht)
Set MyRange = .Range(.Cells(FirstRow,FirstCol),.Cells(LastRow,La stCol))
end with