Help with XLDOWN in VBA

[geewhysee]

I have a macro that generates a pivot table and pastes it into a sheet. I want to do some calculations on the data in that table and then create a chart off of those calculations. Currently I have the VBA below and it has worked fine but the pivot table has started to have a varying number of rows rather than always 48. I want to be able to dynamically generate the chart no matter how many rows the pivot table ends up having. The pivot table data will always start in A64 (with headers in A63 that I don't need) and it has a grand total at the bottom which I don't want so I guess I'll have to use xldown - 1 or something similar. There are multiple tables on the sheet but there are spaces between them.

I think I need to use End XLDOWN in this case but I'm not sure exactly how to do it. Can anyone help?

Range("Pivots!$E64).Formula = "=((C64/B64)/DATA!$Y$5)"
Range("Pivots!$E65).Formula = "=((C65/B65)/DATA!$Y$5)"

... and so on down to

Range("Pivots!$E111).Formula = "=((C111/B111)/DATA!$Y$5)"

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("Pivots!$E$64:$E$111")
ActiveChart.SeriesCollection(1).XValues = "=Pivots!$A$64:$A$111"

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.Parent.Cut
Sheets("Front Sheet").Select

Range("B23").Select
ActiveSheet.Paste

 

[Jerry Sullivan]

Using Range.End(xlDown) or (xlUp) could work, but when working with PivotTables a better option is to use the Properties of the PivotTable that reference defined ranges within the PivotTable.

For your example, PivotTable.DataBodyRange can be used to reference Pivots!$A$64:$D$112. That's the range that has the data values including a grand total row if it exists.

Here's one way to add the formulas using R1C1 notation. Note that there's no need to have separate statements to place a formula in each row.

Sub AddFormulaNextToPivot()
 Dim pvt As PivotTable
 
 '--if more than one PivotTable, modify to reference name
 Set pvt = Sheets("Pivots").PivotTables(1)
 
 With pvt.DataBodyRange
   .Resize(.Rows.Count + pvt.RowGrand, 1).Offset(0, .Columns.Count) _
      .FormulaR1C1 = "=(RC[-2]/RC[-3])/DATA!R5C25"
 End With

End Sub

Try using modifying the rest of your code to add Chart Source references in relative to the .DataBodyRange.

BTW, you might consider using VBA to add a Calculated Field to the PivotTable that uses your formula in Column E. A Calculated Field can't have a dynamic reference to DATA!$Y$5, but you could use the value from $Y$5 in the Calculated Field formula.