View Single Post
 
Old 04-26-2012, 10:15 AM
ibrahimaa ibrahimaa is offline Windows Vista Office 2007
Advanced Beginner
 
Join Date: May 2011
Posts: 35
ibrahimaa is on a distinguished road
Default VBA: Include Dynamic Sheet Name In Pivot Table Source Data

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
Reply With Quote