VBA Pivot table range

[mloveloc]

Hey, new to VBA and I was wondering how I get it to vary the number of pivot lines depending on how much data there is. At the moment it is set to 4 which works most of the time, however sometimes it will only be 3 pivot lines, and the macro reports an error that it is out of range. Essentially, I want the macro to always just include the data that is there. So if the pivot table happens to be 3 columns this time, format the 3 columns, if it is 4, format 4 columns. Here is what I have:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("GROSS_AMT"), "Sum of GROSS_AMT", xlSum
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveWindow.SmallScroll Down:=9
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("E5:E59").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("REP").AutoSort xlDescending _
, "Sum of GROSS_AMT", ActiveSheet.PivotTables("PivotTable1").PivotColumnAxis. _
PivotLines(4), 1
Range("D17:D18").Select
ActiveWindow.SmallScroll Down:=-15
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight2"
ActiveSheet.PivotTables("PivotTable1").ShowTableStyleRowStripes = True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Redemptions"
Range("A1").Select
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=30
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=-48
End Sub

I think the red is where I am having issues.

thanks!

 

[Worf]

Hi

- see if the example below is what you want
- all the selecting is not necessary, we can take care of this later
- a quick reference on the auto sort method: https://msdn.microsoft.com/en-us/library/bb209699(v=office.12).aspx

Sub Loveloc()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.AddDataField pt.PivotFields("GROSS_AMT"), "Sum of GROSS_AMT", xlSum
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("E5:E59").Select
'  pt.DataBodyRange.Columns.Count is the number of data columns
pt.PivotFields("REP").AutoSort 2, "Total Sum of GROSS_AMT", pt.DataBodyRange.Columns.Count, 1
Range("D17:D18").Select
pt.TableStyle2 = "PivotStyleLight2"
pt.ShowTableStyleRowStripes = True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Redemptions"
Range("A1").Select
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=30
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Sheets("Sheet2").Activate
End Sub

 

[mloveloc]

Thank you! That was exactly what I wanted to do! Haha yes, I defiantly need to work on streamlining!