Probably not possible....but I have a spreadsheet I send to multiple clients
where the rows are grouped on levels 1-5 (5 being the most specific of the
data, therefore most rows). I have set up buttons (macro assigned) which
they can use to print just their columns of data.

The titles to the left of the data are repeating, so each individual macro
is simply selecting the appropriate columns, setting print area, and printing.

I find that levels 1, 2 & 3 of data look best when printed in Landscape.
Levels 4 & 5 look best when printed in Portrait. Is there something that I
can add to a Macro in order for it to print Portrait/Landscape based on the
level of grouped rows selected?

Thanks!

A bit confused about what you want .
The macro snippet below will check for the # of rows you have visible. It
will then change your print between portrait and landscape depending on if
there are more than (in this example - using the variable 'iMaxPortrait') 30
visible rows.

'/==================================/
Dim iMaxPortrait As Integer
Dim rng As Range

'max rows to print portrait, if more rows
' change to landscape
iMaxPortrait = 30

'create the range of visible cells to be able to count
' visible rows
Set rng = Range(ActiveSheet.UsedRange.Columns("A").Address)
Set rng = rng.SpecialCells(xlCellTypeVisible)

'change to landscape if visible rows iMaxPortrait
If rng.Count iMaxPortrait Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
'/==================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Kevin W" wrote:

Probably not possible....but I have a spreadsheet I send to multiple clients
where the rows are grouped on levels 1-5 (5 being the most specific of the
data, therefore most rows). I have set up buttons (macro assigned) which
they can use to print just their columns of data.

The titles to the left of the data are repeating, so each individual macro
is simply selecting the appropriate columns, setting print area, and printing.

I find that levels 1, 2 & 3 of data look best when printed in Landscape.
Levels 4 & 5 look best when printed in Portrait. Is there something that I
can add to a Macro in order for it to print Portrait/Landscape based on the
level of grouped rows selected?

Thanks!

I think you're on to exactly what I want. However, one question. Let's use
an example for CLIENTA.

CLIENTA is interested in Columns G:I. I've already selected this range and
called it "CLIENTA". My Macro reads:

Sub PRCLIENTA()
'
Application.Goto Reference:="CLIENTA"
Selection.PrintOut Copies:=1, Collate:=True
Range("G2").Select
End Sub

I'm new to Macros, how can I incorporate both together? I'm getting an
error of "Run-time error '1004': No cells were found."

Thanks, Gary


"Gary Brown" wrote:

A bit confused about what you want .
The macro snippet below will check for the # of rows you have visible. It
will then change your print between portrait and landscape depending on if
there are more than (in this example - using the variable 'iMaxPortrait') 30
visible rows.

'/==================================/
Dim iMaxPortrait As Integer
Dim rng As Range

'max rows to print portrait, if more rows
' change to landscape
iMaxPortrait = 30

'create the range of visible cells to be able to count
' visible rows
Set rng = Range(ActiveSheet.UsedRange.Columns("A").Address)
Set rng = rng.SpecialCells(xlCellTypeVisible)

'change to landscape if visible rows iMaxPortrait
If rng.Count iMaxPortrait Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
'/==================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Kevin W" wrote:

Probably not possible....but I have a spreadsheet I send to multiple clients
where the rows are grouped on levels 1-5 (5 being the most specific of the
data, therefore most rows). I have set up buttons (macro assigned) which
they can use to print just their columns of data.

The titles to the left of the data are repeating, so each individual macro
is simply selecting the appropriate columns, setting print area, and printing.

I find that levels 1, 2 & 3 of data look best when printed in Landscape.
Levels 4 & 5 look best when printed in Portrait. Is there something that I
can add to a Macro in order for it to print Portrait/Landscape based on the
level of grouped rows selected?

Thanks!

On my second try this worked perfectly. Thanks!

"Gary Brown" wrote:

A bit confused about what you want .
The macro snippet below will check for the # of rows you have visible. It
will then change your print between portrait and landscape depending on if
there are more than (in this example - using the variable 'iMaxPortrait') 30
visible rows.

'/==================================/
Dim iMaxPortrait As Integer
Dim rng As Range

'max rows to print portrait, if more rows
' change to landscape
iMaxPortrait = 30

'create the range of visible cells to be able to count
' visible rows
Set rng = Range(ActiveSheet.UsedRange.Columns("A").Address)
Set rng = rng.SpecialCells(xlCellTypeVisible)

'change to landscape if visible rows iMaxPortrait
If rng.Count iMaxPortrait Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
'/==================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Kevin W" wrote:

Probably not possible....but I have a spreadsheet I send to multiple clients
where the rows are grouped on levels 1-5 (5 being the most specific of the
data, therefore most rows). I have set up buttons (macro assigned) which
they can use to print just their columns of data.

The titles to the left of the data are repeating, so each individual macro
is simply selecting the appropriate columns, setting print area, and printing.

I find that levels 1, 2 & 3 of data look best when printed in Landscape.
Levels 4 & 5 look best when printed in Portrait. Is there something that I
can add to a Macro in order for it to print Portrait/Landscape based on the
level of grouped rows selected?

Thanks!

This incorporates both macros and allows you to use one macro for multiple
clients.

'/==================================/
Sub TestMe()
Call PRClient("CLIENTA")
Call PRClient("CLIENTB")
Call PRClient("CLIENTZ")
End Sub
'/==================================/
Sub PRClient(strClientName)
'called from the TestMe macro for each client
Dim iMaxPortrait As Integer
Dim rng As Range

'max rows to print portrait, if more rows
' change to landscape
iMaxPortrait = 30

'create the range of visible cells to be able to count
' visible rows
Set rng = _
Application.Intersect(Range(ActiveSheet.UsedRange. Address), _
Range(strClientName)).Columns("A").SpecialCells(xl CellTypeVisible)

'change to landscape if visible rows iMaxPortrait
If rng.Count iMaxPortrait Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If

'print out range
Range(strClientName).PrintOut Copies:=1, Collate:=True

'to to row 2 of range
Range(rng.Range("A1").Address).Offset(1, 0).Select

End Sub
'/==================================/



--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Kevin W" wrote:

On my second try this worked perfectly. Thanks!

"Gary Brown" wrote:

A bit confused about what you want .
The macro snippet below will check for the # of rows you have visible. It
will then change your print between portrait and landscape depending on if
there are more than (in this example - using the variable 'iMaxPortrait') 30
visible rows.

'/==================================/
Dim iMaxPortrait As Integer
Dim rng As Range

'max rows to print portrait, if more rows
' change to landscape
iMaxPortrait = 30

'create the range of visible cells to be able to count
' visible rows
Set rng = Range(ActiveSheet.UsedRange.Columns("A").Address)
Set rng = rng.SpecialCells(xlCellTypeVisible)

'change to landscape if visible rows iMaxPortrait
If rng.Count iMaxPortrait Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
'/==================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Kevin W" wrote:

Probably not possible....but I have a spreadsheet I send to multiple clients
where the rows are grouped on levels 1-5 (5 being the most specific of the
data, therefore most rows). I have set up buttons (macro assigned) which
they can use to print just their columns of data.

The titles to the left of the data are repeating, so each individual macro
is simply selecting the appropriate columns, setting print area, and printing.

I find that levels 1, 2 & 3 of data look best when printed in Landscape.
Levels 4 & 5 look best when printed in Portrait. Is there something that I
can add to a Macro in order for it to print Portrait/Landscape based on the
level of grouped rows selected?

Thanks!