#1
|
|||
|
|||
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 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 |
#2
|
||||
|
||||
I haven't tested your code but you need to concatenate your SName string variable (and also include ' to allow for spaces within the sheet name):
Code:
"Sheets(Sname)!R1C1:R46C2", Code:
"'" & Sname & "'!R1C1:R46C2", |
#3
|
|||
|
|||
Thanks a lot for the great support that I used to receive from you.
For me as well as all the beginners, this type of support means a lot for us because we spend huge time & efforts trying to fix such things which you can solve it in a minute or less. Thanks again. |
|
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 |