customised printing of multiple pivot charts & tables - unsure how to progress

[Tim]

Hi All,

I have a spreadsheet with multiple pivto table (2 per sheet on 12 sheets)
and charts/graphs (12 per sheet on 2 sheets).

I have created a userform for selecting what to print (ie, which sets of
data, print charts and / or tables) using checkboxes.Can anyone give me a
pointer for the best way to progress from here?!

I could probably sort out a for...next loop to print each one in turn, but i
wondered if there would be any way of compiling all of the bits i want to
print into a single print job... the icing on the cake would be to allow a
print preview (assigned to a separate button on the userform).

Also, whilst trying to sort this out i wondered if it is possible to name a
chart or pivot table as you would a named range (so that if the size of the
pivot table changed, this would also be picked up)? i can't work out how.

Thanks for any help,

Tim

 

[Tom Ogilvy]

You could have the code in your userform format the sheet by hidding rows
and columns for items which won't be printed, then grouping the appropriate
sheet and doing a ActiveWindow.SelectedSheet.PrintOut (or PrintPreview).

You can make the underlying datasources refered to by the pivottables be
dynamic ranges.

Insert Name Define
name: Table1Data
refersto: =Offset(Sheet1!A1,0,0,CountA(A:A),10)

as an example.

 

[Tim]

Thanks Tom, I'll give it a go.

You could have the code in your userform format the sheet by hidding rows
and columns for items which won't be printed, then grouping the appropriate
sheet and doing a ActiveWindow.SelectedSheet.PrintOut (or PrintPreview).

You can make the underlying datasources refered to by the pivottables be
dynamic ranges.

Insert Name Define
name: Table1Data
refersto: =Offset(Sheet1!A1,0,0,CountA(A:A),10)

as an example.

--
Regards,
Tom Ogilvy


but name