Can creating pdf BVA be faster?

[karolina1406]

hi, I am using below code for creating pdf from my excel file but it is extremely slow and often freezes the workbook... any ideas how i can improve it?

Private Sub CommandButton1_Click()
'populating Customer Handover certificate in Excel
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("loa")

sh.Range("b7").Value = "RE: Letter of Authority for Access at:"
sh.Range("c9").Value = Address.Text
sh.Range("c12").Value = LLUC.Text
sh.Range("c13").Value = Floor.Text
sh.Range("c14").Value = Suite.Text
sh.Range("c15").Value = Rack.Text
sh.Range("c16").Value = "First available"
sh.Range("c17").Value = Connector.Text
sh.Range("c18").Value = NoofConnections.Text



    With Sheets("loa").PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    .LeftMargin = Application.CentimetersToPoints(1.5)
    .RightMargin = Application.CentimetersToPoints(0.5)
    .TopMargin = Application.CentimetersToPoints(0.5)
    .BottomMargin = Application.CentimetersToPoints(0.5)
    .HeaderMargin = Application.CentimetersToPoints(0.2)
    .FooterMargin = Application.CentimetersToPoints(0.2)
    .PaperSize = xlPaperA4
    .Orientation = xlPortrait
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1

End With

Me.OrderNumberTX.Value = ""
Me.Address.Value = ""
Me.LLUC.Value = ""
Me.Floor.Value = ""
Me.Suite.Value = ""
Me.Rack.Value = ""
Me.Connector.Value = ""
Me.NoofConnections.Value = ""

LOA.Hide

Sheets("loa").PageSetup.PrintArea = ""
Sheets("loa").PageSetup.PrintArea = "$b$1:$d$34"
Worksheets("loa").PrintPreview


Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

 

[footoo]

What part of the code is slow?
If it's the PageSetup bits, have a look here : excel vba pagesetup slow - Google Search