Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename active sheet with current file name | Excel Programming | |||
looping through current region | Excel Programming | |||
Assigning name to current region | Excel Programming | |||
How to detect current active sheet number | Excel Programming | |||
Copy Current Region | Excel Programming |