How to set up Pivot table in VBA

Hi,

I'm trying to simply create a pivot table using VBA. However, the number of rows is unknown and changes each time the program is run. To counter this, so that my macro selects the entire dataset, I've been attempting to set my range equal to a variable and then plugging the variable into the SourceData.

my headings are in Ranges(A1:E1), so I've been selecting A1, end(xlright) to select all the columns, then end(xldown) to select all the rows. Then setting the selection equal to the variable pivotrange. below is a snip of my code.

I'm about to throw my laptop out the window... so I would really appreciate any help you can offer.

Please Login or Register  to view this content.

Hi, welcome to the forum

I have adjusted your title to something that better describes your problem, and wrapped code tags around your code (see my signature on how to do that)

i wouldnt normally use the method you have for determining pivot range...its rather unpredictable
nevertheless..possible code below

Please Login or Register  to view this content.

im assuming sector exposure worksheet...doesnt exist

Please Login or Register  to view this content.

method if sector exposure already exist

as you did not indicate if you wanted anything else
both methods do not add anything (no fields/rows/columns/filters/values) to the pivot table...just set it up