Sep 22 2022 07:34 AM
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
Sep 22 2022 07:54 AM
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
Sep 22 2022 08:10 AM
Sep 22 2022 08:12 AM
Sep 22 2022 08:29 AM
Sep 22 2022 12:04 PM
Somewhere between the lines
With ActiveSheet.PageSetup
and
End With
insert
' Set print area
.PrintArea = "$A$1:$M$50"
Sep 23 2022 01:18 AM
Sep 23 2022 03:31 AM
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