Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a query for pivot table | Excel Discussion (Misc queries) | |||
Pivot Table From MS Query | Excel Programming | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Programming |