excel VBA Printing Files to crazy sizes

Question

I've been attempting to create a macro that creates a pdf for each worksheet beyond a certain sheet, that being sheet 6 onwards, but when it does create a pdf it creates them at different page sizes which I need them all to be the same size.

When I print and create a pdf in the regular way, it works but when its through the macro it doesn't.

Here is the code from said macro:

Sub exportToPDF()

Dim ws As Worksheet
Dim i As Long
Dim nm As String
Dim folder As String

folder = "\folder\"

For i = 6 To ThisWorkbook.Worksheets.Count
    With ThisWorkbook.Worksheets(i)

nm = .Name

.ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=folder & nm & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next i

MsgBox ("Reports Printed")

End Sub

The size it should be at is A4 but the page being printed is much larger than the other ones which are printed manually when using this macro.

Any help with this would be greatly appreciated, and I can imagine the solution is fairly simple.

Do any of the answers here help?

cybernetic.nomad
– cybernetic.nomad

2025-05-13 16:41:53 +00:00
Commented May 13 at 16:41 — cybernetic.nomad
– cybernetic.nomad, Commented May 13 at 16:41

Michal · Accepted Answer · 2025-05-14 00:31:38Z

1

"The size it should be at is A4" - the size is not specified in your code so why would you expect it to be so? While this might be so by default, it's possible that is has been changed. To make sure that the output size is what you expect, I would suggest specifying it in the first place, something like:

Sub exportToPDF()

    Dim ws As Worksheet
    Dim i As Long
    Dim nm As String
    Dim folder As String

    folder = "C:\test\" ' Update this to your actual folder path

    For i = 6 To ThisWorkbook.Worksheets.Count
        Set ws = ThisWorkbook.Worksheets(i)
        
        With ws.PageSetup
            .PaperSize = xlPaperA4 ' Set to A4; change if needed
            .Orientation = xlPortrait ' or xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False ' Set to 1 if you want to fit to one page tall as well
        End With
        
        nm = ws.Name
        
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=folder & nm & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next i
    MsgBox "Reports Printed"

End Sub

answered May 14 at 0:31

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseph Hannigan May 14 at 8:25

Hi Michal, thanks for your response but it is still not working the way i want it to, ive worked it out and the issue is the zoom part, however, my macro requires the zoom to be variable, i can set it to what i like on the scale to fit on excel its self but i dont know how i can set that in the macro itself

Collectives™ on Stack Overflow

excel VBA Printing Files to crazy sizes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related