Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: VBA Macro - Print page set print area

  1. #1
    Join Date
    31st December 2005
    Posts
    2

    VBA Macro - Print page set print area

    Hello,

    I want to set up two macros that will set my print area to the current selection, set the margins, fit to one page and then either be landscape or portriate depending on which of the two macros I use. I then want to assign the macros to buttons in Excel so I can quickly click the button and me me the trouble of doing all the steps. I have tried to create a macro but the print area gets hard coded into the macro. Here's the code I have (sorry if this is too long...):
    Code:
        Range("D8:E9").Select
        ActiveSheet.PageSetup.PrintArea = "$D$8:$E$9"
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        ActiveSheet.PageSetup.PrintArea = "$D$8:$E$9"
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
        End With
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    I'm sure this could be done a lot more efficiently but I haven't played around with it much yet.

    Also, just to clarify - I want to be able to highlight A1:B12 or C14:M21 and then have the macro look at what I currently have highlighted and set the print area to this. It must be possible, right?

    -Dan
    Last edited by Jack in the UK; December 31st, 2005 at 08:23. Reason: Added VBA Code Tags - jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd June 2005
    Posts
    23

    Re: VBA Macro - Print page set print area

    here is the code. To change it for portrait just change the line .Orientation = xlLandscape to .Orientation= xlPortrait
    Code:
    Public Sub cusPrintArea()
    
    Dim myRange As String
    
    myRange = Selection.Address
    ActiveSheet.PageSetup.PrintArea = myRange
    
    On Error GoTo 1
    1: Exit Sub
    
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st December 2005
    Posts
    2

    Re: VBA Macro - Print page set print area

    Thanks neis!!!



    -Dan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count Page Number within Print Area
    By EricUK in forum EXCEL HELP
    Replies: 12
    Last Post: August 20th, 2014, 09:03
  2. Replies: 1
    Last Post: September 9th, 2005, 22:28
  3. Replies: 4
    Last Post: July 26th, 2005, 01:07
  4. Enlarging print area to fit all to page
    By Chester in forum EXCEL HELP
    Replies: 2
    Last Post: March 14th, 2003, 05:10

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno