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