Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Macro - Print page set print area

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 08:23. Reason: Added VBA Code Tags - jiuk

  • #2
    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
    Berkley Capital Partners
    Real Estate Investment and Development

    Comment


    • #3
      Re: VBA Macro - Print page set print area

      Thanks neis!!!



      -Dan

      Comment

      Trending

      Collapse

      There are no results that meet this criteria.

      Working...
      X