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
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