Forum Discussion

byloom's avatar
byloom
Copper Contributor
Sep 22, 2022

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

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

excel
Macros and VBA

7 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 22, 2022

    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
    • byloom's avatar
      byloom
      Copper Contributor
      Sep 22, 2022
      first of all thank you so much, where i must add the code? thank u for your support and help appreciate

Resources