Printing a Pivot Table from code - Excel 2003

Hi,

I have a pivot table that constantly changes based on what the user chooses
to analyse.

I want the user to be able to print the pivot table at the click of a button
with having to keep setting the print area.

Is there a way to do this programmatically

If yes, how?

Thanks

You shouldn't have to set a print area. If the pivot table is the only
thing on the worksheet, only the pivot table range will print.

Newbie wrote:
> Hi,
>
> I have a pivot table that constantly changes based on what the user chooses
> to analyse.
>
> I want the user to be able to print the pivot table at the click of a button
> with having to keep setting the print area.
>
> Is there a way to do this programmatically
>
> If yes, how?
>
> Thanks
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks but there are other things on the worksheet . . . . any other ideas?
"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> You shouldn't have to set a print area. If the pivot table is the only
> thing on the worksheet, only the pivot table range will print.
>
> Newbie wrote:
>> Hi,
>>
>> I have a pivot table that constantly changes based on what the user
>> chooses to analyse.
>>
>> I want the user to be able to print the pivot table at the click of a
>> button with having to keep setting the print area.
>>
>> Is there a way to do this programmatically
>>
>> If yes, how?
>>
>> Thanks
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

You could use the TableRange2 property to create an address for the
print area. For example:

'==============================
Dim ws As Worksheet
Dim rngPT As Range
Dim strPT As String

Set ws = ActiveSheet
Set rngPT = ws.PivotTables(1).TableRange2
strPT = rngPT.Address

ws.PageSetup.PrintArea = strPT
'=====================

Newbie wrote:
> Thanks but there are other things on the worksheet . . . . any other ideas?
> "Debra Dalgleish" <[email protected]> wrote in message
> news:[email protected]...
>
>>You shouldn't have to set a print area. If the pivot table is the only
>>thing on the worksheet, only the pivot table range will print.
>>
>>Newbie wrote:
>>
>>>Hi,
>>>
>>>I have a pivot table that constantly changes based on what the user
>>>chooses to analyse.
>>>
>>>I want the user to be able to print the pivot table at the click of a
>>>button with having to keep setting the print area.
>>>
>>>Is there a way to do this programmatically
>>>
>>>If yes, how?
>>>
>>>Thanks
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks I'll give it a go
"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> You could use the TableRange2 property to create an address for the
> print area. For example:
>
> '==============================
> Dim ws As Worksheet
> Dim rngPT As Range
> Dim strPT As String
>
> Set ws = ActiveSheet
> Set rngPT = ws.PivotTables(1).TableRange2
> strPT = rngPT.Address
>
> ws.PageSetup.PrintArea = strPT
> '=====================
>
> Newbie wrote:
> > Thanks but there are other things on the worksheet . . . . any other
ideas?
> > "Debra Dalgleish" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>You shouldn't have to set a print area. If the pivot table is the only
> >>thing on the worksheet, only the pivot table range will print.
> >>
> >>Newbie wrote:
> >>
> >>>Hi,
> >>>
> >>>I have a pivot table that constantly changes based on what the user
> >>>chooses to analyse.
> >>>
> >>>I want the user to be able to print the pivot table at the click of a
> >>>button with having to keep setting the print area.
> >>>
> >>>Is there a way to do this programmatically
> >>>
> >>>If yes, how?
> >>>
> >>>Thanks
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>