Print Worksheets in Excel

This code demonstrates the use of printing a form(i.e worksheets) in excel through vba macro. You can also specify some sheets to be of different orientation(like here I have used Portrait for some sheets and Landscape for remaining ones)

Sub PrintWorksheets()
On Error Resume Next
Application.ScreenUpdating = False
Dim i As Integer
Call createarr

printmsg = MsgBox("Do you want to preview the workbook for printing?", vbOKCancel, "Print preview")
If printmsg = 2 Then
End If

Dim sheetarray() As Variant
cnti = -1

For i = 0 To Total_Count - 2
If printyn(i) = "Y" Then
cnti = cnti + 1
ReDim Preserve sheetarray(cnti)
sheetarray(cnti) = sectionname(i)

With Sheets(sectionname(i)).PageSetup

.BlackAndWhite = True
.CenterHorizontally = True
.CenterVertically = False
.LeftMargin = 0.4
.RightMargin = 0.4
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.Orientation = xlPortrait

End With
End If

For i = 0 To Total_Count - 2
If printyn(i) = "Y" And (i = 5 Or i = 9 Or i = 14 Or i = 16 Or i = 21) Then
With Sheets(sectionname(i)).PageSetup
.Orientation = xlLandscape
End With
End If


Application.ScreenUpdating = True
End Sub


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: