Strange problem with 'ClearTable' of pivot table

[sharshra]

I use pivot table in one worksheet to populate values in a table in another sheet thru VBA code. A strange problem is happening The code works well & populates the table by reading pivot table. When 'ClearTable' is used to clear the pivot table, it clears the pivot but it is changing the format of table another sheet. Can the experts advise why this strange problem is happening & how to resolve it?

For example -
- Pivot table - Sheet 1 - text format - times new roman, 11
- Project table - Sheet 2 - text format - calibri, 9

VBA code correctly copies value from pivot table in sheet 1 to project table in sheet 2. Text format of project table is not changed (still calibri, 9). But when 'ClearTable' is done on pivot table in sheet 1, it is changing the format of project table in sheet 2 (times new roman, 11).

 

[sharshra]

Hello Experts,
Please share if you have any advise. It is happening only with few tables!! There are 20+ tables which reads from the pivot & populate. The formatting is corrupted in 1st table, which corresponds to pivot cells in another tab! Code is functioning as expected, but this formatting issue is annoying .

 

[sharshra]

Hello Experts,
Does anyone have a solution for this problem?

 

[sharshra]

I'm done with the remaining programming but this issue is frustrating me! Eagerly looking for solutions / suggestions. Can the experts in this forum respond please?

 

[sharshra]

Code to copy data from pivot table in sheet 1 to table in sheet 2 is as follows. I don't see anything wrong the code here, but it is going wrong with ClearTable format . This minor issue is holding the release. Can someone help to resolve this please?

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] PvtTbl[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]RowRange[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
    Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]strTableRange[/COLOR][COLOR=#303336])([/COLOR][COLOR=#303336]i [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] PvtTbl[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]RowRange[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] i
[/COLOR]</code>

It is correctly filling the table (sheet 2) by reading the pivot (sheet 1).

Clear the pivot (sheet 1) after filling the table.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]PvtTbl[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ClearAllFilters
PvtTbl[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ClearTable
[/COLOR]</code>

Till <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">PvtTbl.ClearAllFilters</code> is done, format in table (sheet 2) remains as calibri. When <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">PvtTbl.ClearTable</code> is done, it clears pivot (sheet 1) but format in table (sheet 2) changes to arial!

 

[sharshra]

Hello Experts,
Can someone help to resolve this issue please? This forum is very resourceful & helped many times before to get a solution quickly . But this question remains unanswered for long time . Wondering if it is too complicated to answer? Or did I not make the problem clear?

 

[SpillerBD]

I think your method is foreign to most using Pivot Tables, macro or otherwise, and why you haven't received a proposed solution.
Are you using custom formats or formatting the Pivot Table at some point? Is a PivotTable Style in use? also is a Table Style in use?
Is Arial the default font for "body" or "header" ?

 

[sharshra]

Glad to see a response . Thanks SpillerBD!!

Please note that I'm using the default format & styles for the pivot table. Even otherwise, not clear why pivot table format should impact format in other tables.

 

[sharshra]

Hello Experts,
Any thoughts on this issue? It is still bothering me