Personalize Output Print based on the las row with data and Printer Setting

[byloom]

Dear All,

i have a woorkbook with N sheets
i have one file where i can select the sheet to print and use a button to print the sheet i select, i can select also multiples sheets.

i am using this macro to print specific sheet of my excel. All those sheet can have different lenght ( row with data) (can go from ROW 1 to Ro 50)

i want the following macro will be able to print for the sheet i select all the data in until the last row with infromation (can be from row 1 to row 4, or from row 1 to row 20...etc) the last column with dat ais colum M for all the sheets...

I want to print the sheet with the following setting too :
Landscape ordinetation
Narrow margins
Fit All columns on one page
Header of the page with the name of the sheet

Hope all is clear

Private Sub Worksheet_Activate()
Dim Sh

Me.ListBoxSh.Clear

For Each Sh In ThisWorkbook.Sheets
Me.ListBoxSh.AddItem Sh.Name
Next Sh

End Sub

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String

With ActiveSheet.ListBoxSh

For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve SheetArray(c)
SheetArray(c) = .List(i)
c = c + 1
End If
Next i
Application.Dialogs(xlDialogPrinterSetup).Show

Sheets(SheetArray()).PrintPreview

'If you'd like to print out
'Sheets(SheetArray()).PrintOut

End With

End Sub

[#2]

@byloom 

Insert the following lines:

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        ' Set the header
        .CenterHeader = "&A"
        ' Set the margins
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        ' Fit to one page wide
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
    End With
    Application.PrintCommunication = True

[#3]

first of all thank you so much, where i must add the code? thank u for your support and help appreciate

[#4]

@byloom 

Above the line

Application.Dialogs(xlDialogPrinterSetup).Show

[#5]

ok thank you so much, and if i want to print a range from like from A1 to M50 how i can set it please? appreciate

[#6]

@byloom 

Somewhere between the lines

    With ActiveSheet.PageSetup

and

    End With

insert

        ' Set print area
        .PrintArea = "$A$1:$M$50"

[#7]

thank you so much, is not working i sent you a dm appreciate

[#8]

@byloom 

You want to apply the print settings to each selected sheet, not to the active sheet (which is the Control Sheet):

Sub Print_Sheets()
    Dim i As Long, c As Long
    Dim SheetArray() As String

    Application.PrintCommunication = False

    With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                With Sheets(.List(i)).PageSetup
                     ' Set print area
                    .PrintArea = "$A$1:$M$50"
                    ' Set the header
                    .CenterHeader = "&A"
                    ' Set the margins
                    .LeftMargin = Application.InchesToPoints(0.25)
                    .RightMargin = Application.InchesToPoints(0.25)
                    .TopMargin = Application.InchesToPoints(0.75)
                    .BottomMargin = Application.InchesToPoints(0.75)
                    .HeaderMargin = Application.InchesToPoints(0.3)
                    .FooterMargin = Application.InchesToPoints(0.3)
                    ' Fit to one page wide
                    .Zoom = False
                    .FitToPagesWide = 1
                    .FitToPagesTall = 0
                    .Orientation = xlLandscape
                End With
                c = c + 1
            End If
        Next i
    End With

    Application.PrintCommunication = True
    Application.Dialogs(xlDialogPrinterSetup).Show
    Sheets(SheetArray).PrintPreview
    'If you'd like to print out
    'Sheets(SheetArray()).PrintOut
End Sub