setting source data for a pivot using variables.

[kevvukeka]

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
sheets.add
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

[p45cal]

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
Sheets.Add
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.