Managing multiple print areas

[Guest]

I have a workbook with multiple worksheets and each worksheet has multiple tables (many are pivot tables). This workbook is viewed by numerous individuals in my office and each person has different viewing needs and I worked that out with a simple navigation worksheet that uses hyperlinks. Now I need to manage the printing of the individual tables. Ideally there would be a "print this table" button at each table to initiate the printing of that particular table. Any ideas?

The next problem would be printing the individual pivot tables with a "print this table" button. Is there anyway to set their print area in dynamically? Such as is done with dynamic ranges? Each of these pivot tables will have fixed columns but the rows will grow/shrink with different choices in the page section of the pivot table.

Many thanks,

Joe

 

[Otto Moehrbach]

Joe
You pretty well said it all regarding the buttons. Just place a button
next to or above or below a table & have a small macro assigned to that
button that sets the range for that table. The last line of each macro
would be something like:
TableRange.Printout
Regarding the setting of the range, that's easy to do in a macro. Say
that you have a table starting in cell A1, it's 4 columns wide, and you know
that the data in Column A goes all the way down to the end of the table, and
you know there are no blank cells in Column A of the table. You would use a
line like this:
Set TableRange=Range("A1",Range("A1").End(xlDown).Offset(0,3)).
If there are blank cells in Column A of the table, then this won't work
and some other method will have to be used to find the bottom of the table.
Please post back if you need help in setting this up. It's real easy if
you know how, but virtually impossible if you don't. HTH Otto

I have a workbook with multiple worksheets and each worksheet has multiple

tables (many are pivot tables). This workbook is viewed by numerous
individuals in my office and each person has different viewing needs and I
worked that out with a simple navigation worksheet that uses hyperlinks. Now
I need to manage the printing of the individual tables. Ideally there would
be a "print this table" button at each table to initiate the printing of
that particular table. Any ideas?

The next problem would be printing the individual pivot tables with a

"print this table" button. Is there anyway to set their print area in
dynamically? Such as is done with dynamic ranges? Each of these pivot tables
will have fixed columns but the rows will grow/shrink with different choices
in the page section of the pivot table.

 

[Guest]

Otto,

Thanks a bunch! That was the ticket.

I did in fact have blanks in the first column but after looking at your code I got around it like this:

Table starts in AE6 and is 6 columns wide,
Set TableRange = Range("AD6", Range("AD6").End(xlDown).Offset(0, 1).End(xlUp).Offset(0, 5))
I was able to find the end of the tale from the bottum up instead of top down.

Thanks again for your help.

Joe

 

[Otto Moehrbach]

Joe
Your code to find the last entry in Column AE is correct but awkward.
If you want to go to the bottom of the sheet and come up in Column AE, do
this:
Set TableRange = Range("AE6", Range("AE" & Rows.Count).End(xlUp).Offset(,5))
Yes, Offset(,5) is correct. The "0" is assumed.
Remember that you are coming from the bottom up so you must not put anything
(cell entry) in Column AE below your table. HTH Otto

Otto,

Thanks a bunch! That was the ticket.

I did in fact have blanks in the first column but after looking at your

code I got around it like this: