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