Pivot table using vba

[pdalal]

I am trying to do a pivot table using VBA.
I have uploaded a sample data sheet and a sample output view.
The only change that I want in the output is I want them to be interleaved.
So I want Units 2014 the Units 2015 then Unit Diff from 2014 ,Unit Diff from 2015 and then % of sales units 2014,% of sales units 2015.
I wasn't able to get it that way in this view.
Also the data set will be much larger so essentially it needs to find the last row on the FY2015 Data sheet and use that as Pivot Data.
I did try to start the code in module1 but am stuck.

Thank you !

[marreco]

try similar code

Please Login or Register  to view this content.

[pdalal]

Thank you !
This helps a lot but i still don't know how to do the difference from YOY like how i laid out in the sample pivot that I did using VBA.
Thoughts?

[marreco]

Do you try record Macros?

[pdalal]

No I couldnt get it to work .can someone help?

[pdalal]

I am trying to do a pivot table using VBA.
I have uploaded a sample data sheet and a sample output view.
The only change that I want in the output is I want them to be interleaved.
So I want Units 2014 the Units 2015 then Unit Diff from 2014 ,Unit Diff from 2015 and then % of sales units 2014,% of sales units 2015.
I wasn't able to get it that way in this view.
Also the data set will be much larger so essentially it needs to find the last row on the FY2015 Data sheet and use that as Pivot Data.
I did try to start the code in module1 but am stuck.

Thank you ! Sample report.xlsm

[Olly]

Does this help, as a starting point?

Please Login or Register  to view this content.

[pdalal]

Thank you ! this helps a lot but when I run the code and it comes to creating the Pivot Cache and it bombs out..thoughts?

[Olly]

It will error if you already have a pivotcache in the workbook based on that data range. Try deleting the existing pivotcache first.

[pdalal]

how do I delete existing Pivot cache?

[Olly]

For manual testing, simply manually delete the pivot table in worksheet "Pivots".

Here, look at my attachment - try running the SampleReport macro.

[pdalal]

It works in the sample report but for some reason not when I plunk it in my workbook.I am going to try and play with it a bit and see why it doesnt work since this pivot is a call function from a different sub.Thanks for now.
I will post if I have any more questions.
Thanks!

[JBeaucaire]

Merged duplicate threads.

[pdalal]

OK So I have been poking around it and figured out that the Pivot Cache crashed since my data is >65000 lines.
Is there anyway for the pivot cache to accept data that is >65000 lines?

Thanks!

[Olly]

Should cope with >65k rows in Excel 2010, no problems. I have pivot caches on 100k+ row data sources...

[pdalal]

Hi Olly,
thanks for the reply.
If I reduce the dataset to say like 60K rows then it creates the pivot but not for>65K rows.Thoughts?

[Olly]

Is the file saved in 97-2003 (.xls) format? Try creating a new workbook in 2007+ format (.xlsm) and see how it works.

[pdalal]

it is saved as Excel 2010 Xlsm.
I have tried saving it diff file /diff module but same result.

[pdalal]

Thanks for you help Olly.I put the PC range address slightly differently and it worked where instead of current region it refers to :

Thank you !!