The following codes working fine. It creates a sheet (in this case it is sheet7) and then manipulate the data in Mysheet to generate the Pivottable in sheet7.
Code:
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Mysheet!R1C1:R46C2", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet7!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
I want our users to use the same code without requesting them to change the sheet name that contains the source data which is “mysheet” in my case. Therefore I have placed the following codes at the Beginning to store the active sheet name in a string that I can use latter:
Code:
Dim Sname As String
Sname = ActiveSheet.Name
However the following codes did not work and I am sure it is something simple. Any suuport will be appreciated. Thanks
Code:
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheets(Sname)!R1C1:R46C2", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet7!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet7").Select