Macro to set print area and print out

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 5, 2009

• #2

Re: Macro to set print area and print

This is not clear but maybe right click the Excel logo just to the left of File on the menu bar and paste in

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWindow.SelectedSheets
    With ws
        LR = .Range("G" & Rows.Count).End(xlUp).Row
        With ws.PageSetup
            .PrintArea = .Range("G1:I" & LR).Address
            .Orientation = xlPortrait
            .Zoom = 100
            .FitToPagesTall = 1
        End With
    End With
Next ws
End Sub

then press ALT + Q.

 

Upvote 0

D

DrPips

New Member

Joined

Aug 14, 2009

Messages

35

• Sep 5, 2009

• #3

Re: Macro to set print area and print

Sorry, I don't understand what you mean by right click the excel logo, i just get a selection of 3 things - Customise quick access, show quick access and minimise the ribbon.

I will add the macro to a form control button when finished.

If I'm not giving enough info, please let me know.

Many Thanks,

Dom

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 5, 2009

• #4

Re: Macro to set print area and print

DrPips said:

Sorry, I don't understand what you mean by right click the excel logo, i just get a selection of 3 things - Customise quick access, show quick access and minimise the ribbon.

I will add the macro to a form control button when finished.

If I'm not giving enough info, please let me know.

Many Thanks,

Dom

Click to expand...

You forgot to mention that you are using Excel 2007.

Press ALT+F11 to open the Visual Basic Editor then double click ThisWorkBook then paste in the code.

 

Upvote 0

D

DrPips

New Member

Joined

Aug 14, 2009

Messages

35

• Sep 6, 2009

• #5

Re: Macro to set print area and print

Sorry, forgot to mention I'm inserting this code into a current macro. The first line is coming up with an error. I've tried taking out the sub, but it;s not working.


Thanks,

Dom

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 6, 2009

• #6

Re: Macro to set print area and print

It doesn't go in another macro. It is standalone event code. If you placed it in the ThisWorkbook module it will fire whenever printing is initiated.

 

Upvote 0

D

DrPips

New Member

Joined

Aug 14, 2009

Messages

35

• Sep 6, 2009

• #7

Re: Macro to set print area and print

Sorry, I think I'm being stupid! When I do alt F11, it comes up with all the macros that are in the sheet, but I can't see anything that says this workbook to double click.

Thanks,

Dom

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 6, 2009

• #8

Re: Macro to set print area and print

Look here

 

Upvote 0