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

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