VBA and pivot table

[thylander]

Hi,

Working on a macro to create a from a database.
- After I made the pivot table on a new sheet I want to change the name of the sheet. How do I add this?
- Right now the macro stops at TableDestination row in the code, why ?
- Will the names I gave for the pivotfields (1,5,6) ensure that these columns are added from the raw data ? (the names of the column is the raw data is not 1,5,6 but Supplier, source and so on).

Please Login or Register  to view this content.

Appreciate your help!

[TMS]

It would be helpful, at least for me, if you posted a sample workbook containing some typical data (not necessarily live data) which could be used to test your code.

Regards

[thylander]

Attached a example file,

Another question, how do i remove the "count of" and "Sum of" in the column headers?

[TMS]

It's probably easier to set at reference to the new worksheet when you create it and change the name at the same time. See the code below.

The code works fine for me with Excel 2007. And yes, it picks up the names of the fields.

Regards

Please Login or Register  to view this content.

[thylander]

Well, that worked fine. Just didn't get the name function to work properly but I used

ActiveSheet.Name instead.

Just a follow up question, if I want to get rid of the grand total in the bottom and remove the "count of" and "sum of" in the column header, how do I do that?

[TMS]

To remove the Grand Total line, add ".ColumnGrand = False":

Please Login or Register  to view this content.

I'm not sure how you get rid of the "Sum of" and "Count of". Probably not difficult, I just do not know how to do it.


Regards

[thylander]

Thx for the help,

Found a piece of code that I think will work the only issue is that the name changes in the header every time.

Please Login or Register  to view this content.

Only problem is how i modify it so it just removes the presceding "sum of" or "Count of" because the column header changes.

Anyone got any ideas ?

[Roger Govier]

Hi

At these 2 Dim items at the beginning of your code, than insert the following code immeditaley after your .DisplayFieldCaptions = False

Please Login or Register  to view this content.

You would also benefit in speed if you set
Application.ScreenUpdating = False
at the beginning of your code, and back to true before End sub

[thylander]

Thanks alot Roger,

Was looking around for different options but this one worked out great!

By anychance do you know how to change so instead of using count of it uses Sum as function ?


BR

[Roger Govier]

Hi

Please Login or Register  to view this content.