200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
hello
I no know why this strange case occurs !!
when I save the file as pdf the size of pdf page doesn't show as in sheet . it shows small in up of left the page. should be fit as in the sheet
actually I search in the internt and found some propeites like this
With PageSetup
.UsedRange.CenterHorizontally = True
UsedRange.CenterVertically = True
.UsedRange.Orientation = xlLandscape
.UsedRange.Zoom = 100
End Withbut doesn't work
any suggestion to fix it,please?
Ali
I'm not near my PC (so can't read your full code) but can say that your opening With statement:
With PageSetupwill could since the PageSetup object needs to have a worksheet specified (like Worksheets("Sheet1").PageSetup).
You could omit it (since VBA will assume) but it's better to be specific.
REVISION (page width and tall added)... I suggest you try this (before you export as a pdf), changes in bold:
Private Sub CommandButton1_Click()
ListBoxToPDF ListBox1, ThisWorkbook.Path & "\ListBoxToPDF.pdf"
Unload Me
End Sub
Sub ListBoxToPDF(listbox As Object, pdf As String)
With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
.Range("A1").Resize(listbox.ListCount, listbox.ColumnCount) = listbox.List
' autofit the columns
.UsedRange.Columns.AutoFit
' draw simple borders around cells
.UsedRange.Borders.LineStyle = xlContinuous
' set up the print page
With .PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Zoom = 250
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdf _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
.Parent.Close False
End With
End Sub
noting UsedRange is not involved in setting up the page (Excel knows it) and adjusting the 250 (percentage zoom) above to suit your needs. You might want to comment out the CenterVertically line.
The revised attached file has those changes incorporated.
Hope this helps.