how to convert Table to PivotTable with VBA

[tta.akmal]

Dear Excel Experts,

Could You be kind to help to solve the riddle, how it is possible to convert Table into Pivot Table in new Sheet.
Attaching Screenshot and file.Table to Pivot Table VBA.jpg

[kev_]

How about starting by recording a macro doing exactly that.
Examine what was recorded and come back with any questions

[tta.akmal]

Ok, i tried as You said. Error code 5....Attaching txt. I made comment where debugger showing error with "*" sign

[kev_]

Code below applies number format to pivot table cells (your code formatted worksheet columns)

Your recorded code has been adapted to make it "general" (every version works)

To see it work, open attached file and use shortcut {CTRL} K
- three identical pivot tables are created

Your workbook
Insert the code in a Standard module in your workbook
Select any cell inside your table before running the code

VERSION1 is based on your recorded code

Please Login or Register  to view this content.

VERSION2 uses a variable for the new pivot table to remove clutter from the code

Please Login or Register  to view this content.

VERSION3 tidies things up a bit more

Please Login or Register  to view this content.

[tta.akmal]

Thank You kev_ that finally worked! But i wonder if it will work as my table located in other cells as in example from G1 to L1?

[kev_]

If you select any cell inside your table it should work regardless of where your table is placed

[tta.akmal]

Kev, i just applied some before learned macroses, somehow code isn't working,

Overall, the logic was to transform exported data to Table, then make a Pivot table From it,as we done.

if it is not diff for You could please SHow me, my mistakes in code. Attaching to error txt.

[tta.akmal]

People of the World, who can help me with my mistake in above Code?

[kev_]

transform exported data to Table, then make a Pivot table

This code does that - run in attached workbook with {CTRL}K
- user is asked by input box to select top left cell of the table range
- Excel table is created (it is assumed that there are headers)
- user asked if pivot table required
- user asked to rename new pivot sheet
- pivot table is created

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[tta.akmal]

Kev Great thank you!, BUT it is not working with 1 st Transforming macros error....

[kev_]

Please mark this thread as solved and begin a new thread - you are WAY beyond what you asked for in the original question

Post#1
how it is possible to convert Table into Pivot Table in new Sheet
- post#4 provided the SOLUTION

Post#7
requirement increased
Overall, the logic was to transform exported data to Table, then make a Pivot table From it,as we done
- post #9 provided the SOLUTION

Post#10
requirement increased
BUT it is not working with 1 st Transforming macro

Please understand that saying that "something does not work" does not provide any of the information required to help you
- you must explain what is not working, where the code is breaking down, what type of error you are getting etc


On the new thread
- explain FULLY what you are trying to achieve and NOT here is something that is broken!
- if you have a macro that works that you want to join onto another macro, attach a workbook to the new thread with BOTH macros working separately
Hopefully that will help you get the answers you want

[tta.akmal]

ok kev, i will post a new question.