View Full Version : setting source data for a pivot using variables.

05-16-2013, 08:37 AM
Hi All,

I am trying to write a VBA code for making a pivot in a excel. I have used variables for sheet names, range. However i am not able to use those variables properly in the below code. Kindly help.

I am working on Excel 2007

Sub pivot()
Dim T As Integer, J As Integer
Dim rng As Range

T = ActiveSheet.Index ' this sheet actually contains the source data which keeps changing
Set rng = Range("A1").CurrentRegion
J = ActiveSheet.Index

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets(T).Range(rng).Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=Sheets(J).Range("A3"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

End sub

05-20-2013, 02:04 AM
Using a sheet's index is not useful as it can change, especially if you move sheets, or add sheets, as you're doing. In fact, you'll find in your code that J and T have the same value!
This is more like what you're trying to do:Dim T As Worksheet, J As Worksheet
Dim rng As Range

Set T = ActiveSheet ' this sheet actually contains the source data which keeps changing
Set rng = T.Range("A1").CurrentRegion
Set J = ActiveSheet
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rng).CreatePivotTable _
TableDestination:=J.Range("A3"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End SubYou don't need to qualify rng since its sheet (its parent) is already one of its properties.