Specify a specific range in pivot table

Question

This is the macro I got when i recorded for pivot table creation.

     ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R2C1:R278C35").CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10

These macro creates a new worksheet for pivot table report but i want the pivot table to be created in the specified sheet say

 activeworkbook.sheets(2)

I was guessing TableDestionation is the path where you have to give the sheet name for pivot table creation but I dont know how to set the path over there

and I'm also Unable to figure it out how to specify a specific range in the pivot table

  SourceData:= "Sheet1!R2C1:R278C35" ' how to specify a range using variables

I have to specify these range

 Sheet1.range("A2:AI" & last_row)

here it takes defualt range like these

 "Sheet1!R2C1:R278C35"

I'm So confused with the RC notation , Please help me with these Thanks

Bruno Leite Bruno Leite · Accepted Answer · 2011-10-13T14:02:44

You can use ConvertFormula, from application, for example

Dim StrAddress As String

StrAddress = "sheet1!" & Application.ConvertFormula(Range("A1:Z300").Address, xlA1, xlR1C1)

Debug.Print StrAddress

In sample range A1:Z300 is converted in R1C1 notation, R is a row and C a column, A1 for example is ROW 1 COLUMN 1 and Z300 is ROW 300 COLUMN 26.

Use StrAddress to create your pivot.

Specify a specific range in pivot table

1 Answers