PDA

View Full Version : [SOLVED] How to create a Macro Pivot Table where the range changes?



roxnoxsox
11-16-2015, 05:00 AM
I'm trying to create a macro which will create a pivot table out of columns A & B in my sheet 'Values0001' on a new sheet.

However, my number of rows will frequently change and I can't work out how to incorporate this into the macro. Does anyone have any suggestions? Here is my current code (with my attempted amendments):


LastRow = Range("A" & Rows.Count).End(xlUp).Row


Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Values0001").Range("A1:B" & LastRow), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("ColumnAHeader")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("ColumnBHeader"), "Sum of ColumnBHeader", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False

Aflatoon
11-16-2015, 05:22 AM
You could use this:


Dim pc As PivotCache
Dim pt As PivotTable




With Sheets("Values0001")
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & .Range("A1").CurrentRegion.Resize(, 2).Address(ReferenceStyle:=xlR1C1), _
Version:=xlPivotTableVersion14)
End With
Set pt = pc.CreatePivotTable(tabledestination:="", DefaultVersion:=xlPivotTableVersion14)
ActiveWorkbook.ShowPivotTableFieldList = True
With pt
With .PivotFields("ColumnAHeader")
.Orientation = xlRowField
.Position = 1
End With
.AddDataField .PivotFields("ColumnBHeader"), "Sum of ColumnBHeader", xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False

roxnoxsox
11-16-2015, 06:14 AM
This worked perfect! It seems to make sense as well so I think I can adjust this if ever needed again. Many thanks!