How to add a printer setting into this VBA Code?

Question

I found the below VBA code on the internet which is very helpful, but I would like to add an extra step to change the settings on the printer to "Fit All Columns on One Page". How Can I do that?

Sub PrintFirstFilterItems()
'downloaded from contextures.com
 'prints a copy of pivot table
 'for each item in
 'first Report Filter field
On Error Resume Next
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set pf = pt.PageFields(1)

If pf Is Nothing Then Exit Sub

For Each pi In pf.PivotItems
  pt.PivotFields(pf.Name) _
        .CurrentPage = pi.Name
  ActiveSheet.PrintOut  'for printing
  'ActiveSheet.PrintPreview  'for testing
Next pi
End Sub

I need the code to change the printer settings to this

I am new on VBA, and all I did was search on google how to change it but nothing worked.

All of the options but the properties im thinking specifically matter here are FitToPagesTall and FitToPagesWide — Marcucciboy2
– Marcucciboy2, Commented Dec 6, 2022 at 16:33

score 1 · Accepted Answer · 2022-12-07 22:01:17Z

1

Try the following...

Application.PrintCommunication = False

With ActiveSheet
    With .PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
End With

Application.PrintCommunication = True

edited Dec 7, 2022 at 22:01

answered Dec 6, 2022 at 16:41

user6585761

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

3 Comments

FroblesC Over a year ago

It worked! Thank you. I inserted the code at the beginning of my code but first it printed an extra page without the printer being set up, and then it printed the pages I wanted fitting in 1 page. I removed ".PrintOut to not have the extra page printed. Thank you so much

user6585761 Over a year ago

That's great, glad I could help. And, actually, I'll remove .PrintOut from the code, since it doesn't really belong there in your case. Cheers!

Marcucciboy2 Over a year ago

Good stuff here

Collectives™ on Stack Overflow

How to add a printer setting into this VBA Code?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related