VBA: Include Dynamic Sheet Name In Pivot Table Source Data

[ibrahimaa]

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.

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:

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

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