How to set PrintOut method to a single a4 page-CodePudding

I have the code below, im trying to make my selection of cells print on one a4 page rather than spread across 6 in a command button that is on a sheet, can anyone point out what im doing wrong? I expected to be able to have a printing window like with saving a sheet or something like that, but this is all i can find. thanks a tonne for your help :)

Private Sub CommandButton3_Click()
    Sheets("Home").PageSetup.PrintArea = "$A$1:$W$44"
    Sheets("Home").PageSetup.Orientation = xlLandscape
    Sheets("Home").PageSetup.FitToPagesWide = 1
    Sheets("Home").PageSetup.FitToPagesTall = 1
    Sheets("Home").PrintPreview (EnableChanges = True)
    'Sheets("Home").PrintOut (Preview = True)
End Sub

Ive looked for other some VBA online and looked up the pagesetup, but i cant seem to find what im after, the code above is the closest ive found, but it makes no difference to the printing process.

CodePudding user response：

Is this what you are trying? I have commented the relevant parts of the code. Let me know if you still have any questions.

Option Explicit

Sub Sample()       
    With ThisWorkbook.Sheets("Home")
        With .PageSetup
            .PrintArea = "$A$1:$W$44"
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        
        '~~> To fit text spanning in different pages change the view
        ActiveWindow.View = xlPageBreakPreview
        
        '~~> Extend the Horizontal Pagebreak to extreme right
        '~~> This mimics what you do when you are dragging the PgBrk
        If .HPageBreaks.Count > 0 Then _
        .HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
        
        '~~> Similarly extend the Vertical Pagebreak to extreme down
        If .VPageBreaks.Count > 0 Then _
        .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        
        .PrintPreview EnableChanges:=True
        
        '~~> Change the view back to normal
        ActiveWindow.View = xlNormalView
    End With
End Sub

BEFORE

and the print preview

AFTER