Complex Pivot table formatting
 

Hi,

I have six pivot tables all chained together on a sheet. Since Excel doesn't
do a good job remembering all the custom formatting that you do to it before
a refresh, I need macros to do the formatting afterwards. Is there is any way
to do this by referring to the different parts and then applying your desired
formatting via code? I'm having a heck of at time decreasing line thickness
between groupings, etc. Since these grouping will change each time the pivot
tables are generated due to new data being populated, I can't create a
standard name reference that will consistently refer to the area that I'd
like to format.

To complicate matters, each group of six pivot table contained on a
worksheet will have a sheet name equal to the page filter chosen on a summary
sheet. Naming references becomes difficult because I won't know the sheet
name where the six pivot tables are located until the user enters in a
pagefilter on the summary sheet and VBA subsequently generates the worksheet
with the six pivot tables (all with the same page filter).

Is this a question of WYSIWYG? What a drag if this is the case. How the
tables are generated is very crude and clients won't find them useful due to
the many busy dark lines between data groupings...

Can anyone provide some insight into matter?

Thanks a million...
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Complex Pivot table formatting
 

I'm usually using "Excel predefined formats" combine with my own formats. I
do it this way:

Sub FormatPivotTable(sh As Worksheet)

sh.PivotTables(1).Format xlReport6
sh.Columns("A:A").WrapText = True
sh.Columns("A:A").ColumnWidth = 9
sh.Columns("B:D").ColumnWidth = 5
sh.Columns("E:E").ColumnWidth = 60
sh.Columns("F:G").ColumnWidth = 9
sh.Columns("H:H").ColumnWidth = 13
sh.Cells.EntireRow.AutoFit
sh.Rows("1:4").RowHeight = 0

End Sub

Vlado


"klysell" wrote:

Hi,

I have six pivot tables all chained together on a sheet. Since Excel doesn't
do a good job remembering all the custom formatting that you do to it before
a refresh, I need macros to do the formatting afterwards. Is there is any way
to do this by referring to the different parts and then applying your desired
formatting via code? I'm having a heck of at time decreasing line thickness
between groupings, etc. Since these grouping will change each time the pivot
tables are generated due to new data being populated, I can't create a
standard name reference that will consistently refer to the area that I'd
like to format.

To complicate matters, each group of six pivot table contained on a
worksheet will have a sheet name equal to the page filter chosen on a summary
sheet. Naming references becomes difficult because I won't know the sheet
name where the six pivot tables are located until the user enters in a
pagefilter on the summary sheet and VBA subsequently generates the worksheet
with the six pivot tables (all with the same page filter).

Is this a question of WYSIWYG? What a drag if this is the case. How the
tables are generated is very crude and clients won't find them useful due to
the many busy dark lines between data groupings...

Can anyone provide some insight into matter?

Thanks a million...
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Complex Pivot table formatting
 

Thanks Vlado.

I appreciate your input. Every bit of help has helped me with my general
direction.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Vlado Sveda" wrote:

I'm usually using "Excel predefined formats" combine with my own formats. I
do it this way:

Sub FormatPivotTable(sh As Worksheet)

sh.PivotTables(1).Format xlReport6
sh.Columns("A:A").WrapText = True
sh.Columns("A:A").ColumnWidth = 9
sh.Columns("B:D").ColumnWidth = 5
sh.Columns("E:E").ColumnWidth = 60
sh.Columns("F:G").ColumnWidth = 9
sh.Columns("H:H").ColumnWidth = 13
sh.Cells.EntireRow.AutoFit
sh.Rows("1:4").RowHeight = 0

End Sub

Vlado


"klysell" wrote:

Hi,

I have six pivot tables all chained together on a sheet. Since Excel doesn't
do a good job remembering all the custom formatting that you do to it before
a refresh, I need macros to do the formatting afterwards. Is there is any way
to do this by referring to the different parts and then applying your desired
formatting via code? I'm having a heck of at time decreasing line thickness
between groupings, etc. Since these grouping will change each time the pivot
tables are generated due to new data being populated, I can't create a
standard name reference that will consistently refer to the area that I'd
like to format.

To complicate matters, each group of six pivot table contained on a
worksheet will have a sheet name equal to the page filter chosen on a summary
sheet. Naming references becomes difficult because I won't know the sheet
name where the six pivot tables are located until the user enters in a
pagefilter on the summary sheet and VBA subsequently generates the worksheet
with the six pivot tables (all with the same page filter).

Is this a question of WYSIWYG? What a drag if this is the case. How the
tables are generated is very crude and clients won't find them useful due to
the many busy dark lines between data groupings...

Can anyone provide some insight into matter?

Thanks a million...
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557