Microsoft Office Forums VBA: Include Dynamic Sheet Name In Pivot Table Source Data

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2012, 10:15 AM
ibrahimaa ibrahimaa is offline VBA: Include Dynamic Sheet Name In Pivot Table Source Data Windows Vista VBA: Include Dynamic Sheet Name In Pivot Table Source Data Office 2007
Advanced Beginner
VBA: Include Dynamic Sheet Name In Pivot Table Source Data
 
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
  #2  
Old 04-27-2012, 04:21 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline VBA: Include Dynamic Sheet Name In Pivot Table Source Data Windows 7 32bit VBA: Include Dynamic Sheet Name In Pivot Table Source Data Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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",
becomes:
Code:
"'" & Sname & "'!R1C1:R46C2",
Reply With Quote
  #3  
Old 04-28-2012, 12:27 AM
ibrahimaa ibrahimaa is offline VBA: Include Dynamic Sheet Name In Pivot Table Source Data Windows Vista VBA: Include Dynamic Sheet Name In Pivot Table Source Data Office 2007
Advanced Beginner
VBA: Include Dynamic Sheet Name In Pivot Table Source Data
 
Join Date: May 2011
Posts: 35
ibrahimaa is on a distinguished road
Default

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

Thread Tools
Display Modes


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
VBA: Include Dynamic Sheet Name In Pivot Table Source Data 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 Tables 1 10-10-2006 08:59 AM


All times are GMT -7. The time now is 11:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft