VBA: Include Dynamic Sheet Name In Pivot Table Source Data

ibrahimaa · #1 04-26-2012, 10:15 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
sheet 2 data highlight in sheet 1	gsrikanth	Excel	1	04-21-2012 06:25 PM
Help with a pivot table please!	natsha	Excel	1	02-16-2012 12:41 PM
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
pivot table source data	hannu	Excel	0	07-03-2010 04:54 AM
Include headers in table of contents	bwhight	Word	1	10-10-2006 08:59 AM