kevvukeka
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
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
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