none
Set print area in VBA so even i change my printer my print area would be the same RRS feed

  • Question

  • Hi

    I am just beginner in Excel VBA.

    i am creating one excel file for common use to generate reports.

    I am stuck as i change my printer my excel page format is changing and all my format disappear.

    I want to write the code in Excel VBA so even i change the printer it want affect my formating.

    I am using Excel 2003.

    pls let me know how i can write the code in VBA for it.

    Thanks

    • Changed type Reed KimbleMVP Tuesday, February 21, 2017 3:10 PM asks a question
    • Moved by Reed KimbleMVP Tuesday, February 21, 2017 3:11 PM excel vba posted in VB.Net language
    Tuesday, February 21, 2017 2:16 PM

All replies

  • You've posted to the VB.Net forums.  VBA is a different language so I'll move this to the Excel for Developers forum.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, February 21, 2017 3:11 PM
  • Hi MahiLsk,

    you had mentioned that you want to set print area in VBA.

    for that you can use page setup.print area property.

    Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$5"
    

    Reference:

    PageSetup.PrintArea Property (Excel)

    below is another example with formatting.

    Sub Set_Print_Area()
    
     Application.ScreenUpdating = False
     ActiveWindow.SmallScroll Down:=-24
     Range("A1:Q200").Select
     With ActiveSheet.PageSetup
     .PrintTitleRows = ""
     .PrintTitleColumns = ""
     End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$200"
    With ActiveSheet.PageSetup
     .LeftHeader = ""
     .CenterHeader = ""
     .RightHeader = ""
     .LeftFooter = ""
     .CenterFooter = ""
     .RightFooter = ""
     .LeftMargin = Application.InchesToPoints(0.25)
     .RightMargin = Application.InchesToPoints(0.25)
     .TopMargin = Application.InchesToPoints(0.5)
     .BottomMargin = Application.InchesToPoints(0.5)
     .HeaderMargin = Application.InchesToPoints(0.5)
     .FooterMargin = Application.InchesToPoints(0.5)
     .PrintHeadings = False
     .PrintGridlines = True
     .PrintComments = xlPrintNoComments
     .PrintQuality = 600
     .CenterHorizontally = False
     .CenterVertically = False
     .Orientation = xlLandscape
     .Draft = False
     .PaperSize = xlPaperLegal
     .FirstPageNumber = xlAutomatic
     .Order = xlDownThenOver
     .BlackAndWhite = False
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = False
     .PrintErrors = xlPrintErrorsDisplayed
     End With
     ActiveWindow.SelectedSheets.PrintPreview
     ActiveWindow.SmallScroll Down:=-15
     ActiveWindow.LargeScroll ToRight:=-1
     Range("A1").Select
     Application.ScreenUpdating = True
     End Sub

    Reference:

    Set Print Area using VBA

    you need to run the code every time after you change the printer or you can run the code before printing to make sure about formatting.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 22, 2017 1:54 AM
    Moderator