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
|