Resizing Pivot table column
 

Hi,

A bit of a newbie question. I've got to write a macro that will refresh
all the Pivot tables, spread over a large number of worksheets, in a
workbook & then to print them. That bit I've managed to do using :-

<code
Dim ws As Worksheet
Dim row As Integer
Dim PivotList As Integer
Dim X As Integer

row = 2

Sheets("Print Pivots").Select ' this worksheet contains a list of
the worksheets that need updating
' Loop through worksheets that need printing
Do Until Cells(row, 1).Value = ""
Set ws = Worksheets(Cells(row, 1).Value)
ws.Select


' Get the number of Pivot tables on the worksheet
PivotList = ActiveSheet.PivotTables.Count
' Update each Pivot table in the worksheet
For X = 1 To PivotList
ws.PivotTables(X).PivotCache.Refresh
Next

ActiveSheet.PrintOut
'ws.PrintPreview '- use for testing


'get next pivot table location
Sheets("Print Pivots").Select
row = row + 1
Loop
</code

However one of the Pivot Tables has more data in the same column
beneath it & after the refresh the column width shrinks to fit the
Pivot Table & this other data appears as #########. Any ideas on the
best way of retaining the original column's width?

Thanks in advance,

Andee

Resizing Pivot table column
 

autofit the column after you update the pivottable.

--
Regards,
Tom Ogilvy


" wrote:

Hi,

A bit of a newbie question. I've got to write a macro that will refresh
all the Pivot tables, spread over a large number of worksheets, in a
workbook & then to print them. That bit I've managed to do using :-

<code
Dim ws As Worksheet
Dim row As Integer
Dim PivotList As Integer
Dim X As Integer

row = 2

Sheets("Print Pivots").Select ' this worksheet contains a list of
the worksheets that need updating
' Loop through worksheets that need printing
Do Until Cells(row, 1).Value = ""
Set ws = Worksheets(Cells(row, 1).Value)
ws.Select


' Get the number of Pivot tables on the worksheet
PivotList = ActiveSheet.PivotTables.Count
' Update each Pivot table in the worksheet
For X = 1 To PivotList
ws.PivotTables(X).PivotCache.Refresh
Next

ActiveSheet.PrintOut
'ws.PrintPreview '- use for testing


'get next pivot table location
Sheets("Print Pivots").Select
row = row + 1
Loop
</code

However one of the Pivot Tables has more data in the same column
beneath it & after the refresh the column width shrinks to fit the
Pivot Table & this other data appears as #########. Any ideas on the
best way of retaining the original column's width?

Thanks in advance,

Andee

Resizing Pivot table column
 

Thanks for the reply. Is there a way of telling which columns a pivot
table is using? I don't want to have to hardcode the column.

Andee