Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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...):
    VB:
    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 07: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
    VB:
    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: 1 Week Ago, 08:03
  2. Replies: 1
    Last Post: September 9th, 2005, 21:28
  3. Replies: 4
    Last Post: July 26th, 2005, 00:07
  4. Enlarging print area to fit all to page
    By Chester in forum EXCEL HELP
    Replies: 2
    Last Post: March 14th, 2003, 04: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