PDA

View Full Version : [SOLVED] Pivot Table Flexible Source



clark8529
02-21-2017, 04:55 PM
Hi,
Is there a way to make this line flexible as in coded in selection format instead of hardcoded Row # and Column #?


SourceData:="'Source'!R1C1:R145C7"

YasserKhalil
02-21-2017, 06:23 PM
Try this

Sub Test() Dim sourceData As Range
Set sourceData = Worksheets("Source").Range("A1:G" & Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row)
End Sub

clark8529
02-21-2017, 06:58 PM
Try this

Sub Test() Dim sourceData As Range
Set sourceData = Worksheets("Source").Range("A1:G" & Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row)
End Sub


Set sourceData = Worksheets("Source").Range("A1:G" & Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row) doesn't work if the data is not starting at cell and ends at G column. What I am looking for is more like the this.



add0 = ActiveCell.address
add1 = ActiveCell.End(xlDown).Offset(, 2).address
add2 = ActiveCell.End(xlDown).Offset(3, 0).address
Range(add0, add1).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range(add0).CurrentRegion.address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=Sheets("Sheet1").Range(add2).CurrentRegion.address(ReferenceStyle:=xlR1C1), TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14


Even with that, I am still bounded by xlR1C1 at referencestyle. I am also attempting to make TableDestination to be flexible as well but the code in TableDestination doesn't work when it is not in column A.

The background of the problem:
1. I have data filled from A2:C16.
2. A1:C1 is filled with the data's header.
3. Create pivot table 3 rows below A16.
4. I have another sheet, where this time the data is filled from E2:G16. E1:G1 is filled with data's header.
5. Create a pivot table of the data 3 rows below E16.

The data set will not be always the same place but I am requiring the pivot table to be always 3 rows below the source data.

clark8529
02-21-2017, 08:26 PM
And I made it!.:cool:
It is working. Thanks to for helping.



i=1
add0 = ActiveCell.address
label1 = ActiveCell.Value
label2 = ActiveCell.Offset(0, 1).Value
label3 = ActiveCell.Offset(0, 2).Value
add1 = ActiveCell.End(xlDown).Offset(, 2).address
add2 = ActiveCell.End(xlDown).Offset(3, 0).address
Range(add0, add1).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range(add0).CurrentRegion.address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=Sheets("Sheet1").Range(add2).CurrentRegion.address(ReferenceStyle:=xlR1C1), TableName:="PivotTable" & i, DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
With ActiveSheet.PivotTables("PivotTable" & i).PivotFields(label1)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable" & i).AddDataField ActiveSheet.PivotTables( _
"PivotTable" & i).PivotFields(label3), "Sum of " & label3, xlSum
With ActiveSheet.PivotTables("PivotTable" & i).PivotFields("Sum of " & label3)
.Caption = "Average of " & label3
.Function = xlAverage
End With