vba pivot table variable range data source

[BioExcel]

Hi all,

have an issue with creating pivottables with a variable source range in rows.
source data is sheet "Filter"
range of the data = B2 to L.....
row 2 having headers for the pivto. B= "DL", C= "Place", K= "Departed", L = "Arrived"

the code should create 2 different pivot's.
C = always row label
K =column label in pivot "Departed", L= column label in pivot "Arrived".
Count o DL (B) is value in both pivot.

Selection for variable range should be correct as below:

Dim lr As Range
lr = Sheets("Filter").Range("B3" & Rows.Count).End(xlUp).Row

anyone that can help me with implementing this into the creation of a pivot. Or is there a better solution?

thx ahead.

 

[Jerry Sullivan]

...Or is there a better solution?

Hi BioExcel,

Instead of using VBA to create your PivotTable from scratch each time, consider using a template PivotTable that is set up exactly like you want it.

That allows you to easily modify your template without the need to modify your VBA code.

Your macro can copy-paste the template into the desired location then change its data source to your new data source.

You can't have an unpopulated PivotTable for your template however you can make a pre-formatted PivotTable template that has a header row and 1 row of example data.

If you really want to use VBA to create the Pivot too, you can record a macro while doing the steps manually then generalize the code for the dynamic size of the data source.

For your code snippet, change "B3" to just "B".

lr = Sheets("Filter").Range("B" & Rows.Count).End(xlUp).Row

You can find lots of examples on this site and elsewhere by searching on keywords....
VBA PivotCaches.Create