Macro to control Print area/view

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a document that is about 5,000 rows and 25 columns. In some cases I
need to print all content, and in other cases I need only to print the left
20 cloumns. I currently do this by goint into print view and moving the blue
lines into the appropriate places. I am not very familiar with Macros but
have been led to believe that this can be done using them by inserting a 2
buttons, one with a print macro for the first print view and another with the
condensed print view. I came up with an alternative solution which was to
create a seccond sheet where sheet 2 cell a1 = sheet 1 cell a1.... etc
througout the sheet area then set the print area settings accordingly, but I
am looking for a cleaner solution and some vba experience.
 
These two macros (which you can put behind buttons if you want) should do
what you asked for...

Sub SetPrintArea20Columns()
Dim LastRow As Long
With ActiveSheet
.ResetAllPageBreaks
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
.PageSetup.PrintArea = "$A$1:$T$" & LastRow
End With
End Sub

Sub SetPrintArea25Columns()
Dim LastRow As Long
With ActiveSheet
.ResetAllPageBreaks
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
.PageSetup.PrintArea = "$A$1:$Y$" & LastRow
End With
End Sub
 
Have a look at View>Custom Views

Set up two views and switch from one to the other.

Record a macro and assign to a button.


Gord Dibben MS Excel MVP
 
Back
Top