Printing Macro

Hi There,

I have 6 tables on a worksheet - these are ranges:

B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137

Now, sometimes I will have notes at the side of the table - I put these into
column J, so that any notes for the first tabe would appear in a cell in the
range J2:J22 ; any notes for the second table would appear in a cell in the
range J25:J45 ; etc.

What I want is a macro that will print each of the 6 tables above on a
separate sheet (landscape, centred horizontally & vertically, with Row &
column Headings), and if the table has notes in column J, these should be
displayed on the same sheet. I.e. if the table does not have any notes in
column J, then I don't want to see column J appear in the printout, as this
reduces unneccessarily the size of the text in the printout.

So I need a macro which prints out each table, but before doing so does a
search on the relative range in Column J (for that table) and decides whether
there are notes in that range and therefore whether to include column J in
the printout for that table.

Thanks for any help,

Gary T.

Dim rng as Range, rng1 as rng, rng2 as rng
Dim ar as Range
set rng = Range("B2:I22, B25:I45,B48:I68, B71:I91, B94:I114, B117:I137")
for each ar in rng.Areas
set rng1 = ar.offset(0,8).Resize(,1)
if application.CountA(rng1) = 0 then
rng2 = ar
else
rng2 = ar.Resize(,ar.columns.count+1)
End if
Activesheet.PageSetup.PrintArea = rng2.Address(external:=True)
Activesheet.Printout
Next

You can turn on the macro recorder while you manually make you print
settings and get the code you need for that.

--
Regards,
Tom Ogilvy



"Gary T" <[email protected]> wrote in message
news:[email protected]...
> Hi There,
>
> I have 6 tables on a worksheet - these are ranges:
>
> B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137
>
> Now, sometimes I will have notes at the side of the table - I put these
into
> column J, so that any notes for the first tabe would appear in a cell in
the
> range J2:J22 ; any notes for the second table would appear in a cell in
the
> range J25:J45 ; etc.
>
> What I want is a macro that will print each of the 6 tables above on a
> separate sheet (landscape, centred horizontally & vertically, with Row &
> column Headings), and if the table has notes in column J, these should be
> displayed on the same sheet. I.e. if the table does not have any notes in
> column J, then I don't want to see column J appear in the printout, as
this
> reduces unneccessarily the size of the text in the printout.
>
> So I need a macro which prints out each table, but before doing so does a
> search on the relative range in Column J (for that table) and decides
whether
> there are notes in that range and therefore whether to include column J in
> the printout for that table.
>
> Thanks for any help,
>
> Gary T.

Hi Gary
Try this, it is set up to have two subs, the first one calls the second with
the range of each table, the second when called checks the relevant column J
cells for valid text (spaces are ignored) and adjust print accordingly.

Assumes you are the active worksheet contains the tables, if not then you
need to modify to suit.


' first routine to set range for tables first value = row 1 second value =
last row
Sub PrintTables()
Call PrintRange(2, 22)
Call PrintRange(25, 45)
Call PrintRange(48, 68)
Call PrintRange(71, 91)
Call PrintRange(94, 114)
Call PrintRange(117, 137)
End Sub

' routine to print table testing if j startRow to EndRow has text
Sub PrintRange(SRow As Long, ERow As Long)
Dim ir As Long, iNotes As Boolean
iNotes = False
For ir = SRow To ERow
If Len(Trim(Range("J" & ir).Text)) > 0 Then iNotes = True
Next ir
If iNotes Then
MsgBox "Notes"
ActiveSheet.PageSetup.PrintArea = "B" & SRow & ":J" & ERow
Else
ActiveSheet.PageSetup.PrintArea = "B" & SRow & ":I" & ERow
End If
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub


--
Cheers
Nigel



"Gary T" <[email protected]> wrote in message
news:[email protected]...
> Hi There,
>
> I have 6 tables on a worksheet - these are ranges:
>
> B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137
>
> Now, sometimes I will have notes at the side of the table - I put these
into
> column J, so that any notes for the first tabe would appear in a cell in
the
> range J2:J22 ; any notes for the second table would appear in a cell in
the
> range J25:J45 ; etc.
>
> What I want is a macro that will print each of the 6 tables above on a
> separate sheet (landscape, centred horizontally & vertically, with Row &
> column Headings), and if the table has notes in column J, these should be
> displayed on the same sheet. I.e. if the table does not have any notes in
> column J, then I don't want to see column J appear in the printout, as
this
> reduces unneccessarily the size of the text in the printout.
>
> So I need a macro which prints out each table, but before doing so does a
> search on the relative range in Column J (for that table) and decides
whether
> there are notes in that range and therefore whether to include column J in
> the printout for that table.
>
> Thanks for any help,
>
> Gary T.

Nigel,

Thanx,

That worked perfectly

Regards,

Gary.