Create Pivot table using VBA

[ChrisKad]

Hi,

I'm trying to create a Pivot table but I have no idea how. I looked online on how to do it but I don't understand the explanations.

Can anyone please help me. I have 5 items that need to go in the table:

1) Consultant (Left-most side of the table)
2) Margin
3) Interim
4) X
5) Y

Items 2-5 are amounts that should appear from left to right in the columns after the Consultants. So Consultants is the first column, Margin is the second etc.

Any help will be appreciated. Thank you

Chris

[mancubus]

the best way to start is recording a macro while you are manually creating the table.

then post the recorded macro if you want something more...

[Paul_Hossler]

Also don't forget to capture in the macro recorder any options, such as number formats, sort order, table layout, etc.

You could make a dry run to make sure that the PT is the way you want it, and then record one for real

The recorder will capture the exact ranges that you have in your initial data ("A1:Z26"), but the challenge (not hard) is to make it more general

We can help with that

[ChrisKad]

Hi guys thanks. I did it and it worked.

I just have a question. How do you end up remembering the code for making a pivot table? It seems very long and complex. I'm unable to fully understand and remember it for future use.

Ofcourse I can always copy and paste the code I just made and make the necessary adjustments but I won't be able to remember off by heart.

Thanks a lot for your patience guys.

Chris

[Paul_Hossler]

How do you end up remembering the code for making a pivot table?

I use the macro recorder and then generalize the recorded code, or

I use a wrapper function that I created for me that suits the way I prefer to work

'creates PT from .CurrentRegion around 'PivotRange' and put it on a new 'PivotSheet A1' and names it 'PivotName'
'   pass single value or array of PivotDataFields
'       and/or PivotRowFields and/or PivotRowFields and/or PivotPageFields
'create PT (Input range, output sheet name, PivotName,
'   array(data), array (rows), array (cols), array (pages),
'   quick format = all data = sum, grandtotals and subtotals, shading on total lines
'
'data, row, column, page items can be "LIST_NAME=NEW_NAME;NUMBERFORMAT"

Function PT_Create(PivotRange As Range, PivotSheet As String, PivotName As String, PivotDataFields As Variant, _
        Optional PivotRowFields As Variant, Optional PivotColFields As Variant, Optional PivotPageFields As Variant, _
        Optional PivotQuickFormat As Boolean = True) As PivotTable