Pivot cache range/Source data.. Pls help me out.
Posted by Mr Peter Pettersson on August 28, 2001 12:37 AM
To all pros building makros:
I have a problem to make a pivot out of a selected range. As the range for the pivot cache variates from
time to time I'll run the makro. I want the source
data to be the range selected.
Can anyone find me a solution to this problem?
Code:
Worksheets("Blad1").Activate
Range("B1").Select
Selection.End(xlDown).Activate
ActiveCell.CurrentRegion.Select
(This command selects the range I want in my pivottable)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
' "Blad1!R10C2:R20C8").CreatePivotTable TableDestination:=range("J1"), _
' TableName:="Pivottabell2"
'ActiveSheet.PivotTables("Pivottabell2").SmallGrid = False
..........
Thank U!
Posted by Barrie Davidson on August 28, 2001 7:11 AM
Peter, try declaring a variable at the beginning of your code and then use that variable to refer to your selected range.
'Variable declaration
Dim PivotSource As String
'More of your code
ActiveCell.CurrentRegion.Select
PivotSource = Selection.Address
'More of your code
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= PivotSource.CreatePivotTable TableDestination:=range("J1"), _
TableName:="Pivottabell2"
ActiveSheet.PivotTables("Pivottabell2").SmallGrid = False
Hope this helps you out.
Regards,
Barrie
Posted by Pierre on August 28, 2001 9:44 PM
Hello, I am not a pro but I might help you.
Modify your code with:
Dim MyRange as Range
Worksheets("Blad1").Activate
Range("B1").end(xlDown).select
MyRange=selection.currentRegion
ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= MyRange.CreatePivotTable
etc. etc.
To all pros building makros: (This command selects the range I want in my pivottable)
