Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default Pivot wrt active sheet & current region

Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"wr!R1C1:R638C31", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

Would like to modify SourceData:= "wr!R1C1:R638C31" to something which would take data from my active sheet & current region so that i can use macro for different sheet name and different data range

Pls help - Thxs

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Pivot wrt active sheet & current region

Hi,

Am Mon, 20 Jun 2016 22:07:19 -0700 (PDT) schrieb ANG:

Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"wr!R1C1:R638C31", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

Would like to modify SourceData:= "wr!R1C1:R638C31" to something which would take data from my active sheet & current region so that i can use macro for different sheet name and different data range


try:
Dim sStr As String

With Sheets("Sheet2")
sStr = "'" & .Name & "'!" & .UsedRange.Address
End With

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
sStr, Version:=4).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=4

You only have to modify the sheet name in the With statement.


Regards
Claus B.
--
Windows10
Office 2016
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rename active sheet with current file name [email protected] Excel Programming 5 July 11th 06 11:29 AM
looping through current region buffyslay Excel Programming 2 February 1st 06 06:58 PM
Assigning name to current region KDJ Excel Programming 2 August 21st 05 04:18 PM
How to detect current active sheet number Mike Chen Excel Programming 3 April 3rd 04 04:41 PM
Copy Current Region Rohit Thomas[_2_] Excel Programming 1 December 4th 03 02:55 AM


All times are GMT +1. The time now is 02:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"