Print Landscape/Portrait based on level of grouped data selected

K

Kevin W

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!
 
G

Gary Brown

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
'/==================================/
 
K

Kevin W

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
 
K

Kevin W

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
'/==================================/
 
G

Gary Brown

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(xlCellTypeVisible)

'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
'/==================================/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top