Printing and VBA

[longytravel]

Morning,

I have the following code

Private Sub CommandButton4_Click()
ActiveSheet.PageSetup.Orientation = xlLandscape
Range("B2:J30").PrintOut
End Sub

Columns H, I and J are printed on a second sheet

What code do i need to get in so that excel fits all columns to into the width of the page when printed?

As ever any help greatfullyl recieved!

Thanks

 

[Comfy]

Private Sub CommandButton4_Click()
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
End With
Range("B2:J30").PrintOut
End Sub

 

[longytravel]

Thanks Comfy......

I'm afraid its still printing over two pages?
Any other thoughts?

 

[bensonsearch]

adapt as needed

[COLOR=#0000ff]Public[/COLOR] [COLOR=#0000ff]Sub[/COLOR] cusPrintArea() 
     
    [COLOR=#0000ff]Dim[/COLOR] myRange [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR] 
     
    myRange = "B2:J30"
    ActiveSheet.PageSetup.PrintArea = myRange 
     
    [COLOR=#0000ff]On Error Goto[/COLOR] 1 
    1: Exit [COLOR=#0000ff]Sub[/COLOR] 
     
    [COLOR=#0000ff]With[/COLOR] 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 = [COLOR=#0000ff]False[/COLOR] 
        .PrintGridlines = [COLOR=#0000ff]False[/COLOR] 
        .PrintComments = xlPrintNoComments 
        .PrintQuality = 600 
        .CenterHorizontally = [COLOR=#0000ff]False[/COLOR] 
        .CenterVertically = [COLOR=#0000ff]False[/COLOR] 
        .Orientation = xlLandscape 
        .Draft = [COLOR=#0000ff]False[/COLOR] 
        .PaperSize = xlPaperLetter 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = [COLOR=#0000ff]False[/COLOR] 
        .Zoom = [COLOR=#0000ff]False[/COLOR] 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
        .PrintErrors = xlPrintErrorsDisplayed 
    [COLOR=#0000ff]End With[/COLOR] 
     
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=[COLOR=#0000ff]True[/COLOR] 
     
[COLOR=#0000ff]End Sub[/COLOR]

enjoy