After saving a pdf with one macro . Another macro will no longer PrintOut

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Hello , After I use the Macro titled SaveRangeAsPDF the macro titled Printestimate will not Printout to the printer .When I run the subs in this order the PrintEstimate sub will quickly show a box that says processing but the printer icon does not show 1 and nothing prints.

If I use the PrintEstimate sub before the SaveRangeAsPDF I can use it as many times as I want with no issue and it works perfectly.

Ideally I would like to combine these two subs into one and be able to run it as many times as I want. This is due to me changing data and then wanting to save pdf and printout .
Any help would be greatly appreciated.

Sub SaveRangeAsPDF()

Dim saveLocation As String
Dim rng As Range

With ActiveSheet.PageSetup

.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
'This is the file destination and range
saveLocation = "/Users/boauman/Desktop/Bobs Personal/Estimate pdf/"
Set rng = Range("$A$108:$F$160")


'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=saveLocation & Range("B1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


ActiveSheet.PageSetup.Zoom = 87

End Sub


Sub Printestimate()

With ActiveSheet.PageSetup

.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.Goto Reference:="ESTIMATE"
ActiveSheet.PageSetup.PrintArea = "$A$108:$F$160"

Range("A108:F160").Select
ActiveSheet.PageSetup.PrintArea = "$A$108:$F$160"
Selection.PrintOut Copies:=1, Collate:=True
ActiveSheet.PageSetup.Zoom = 87
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

rlv01

Well-known Member
Joined
May 16, 2017
Messages
798
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I tried your code and It works for me. PrintEstimate prints after SaveRangeAsPDF is run. I had to comment out this line

VBA Code:
Application.Goto Reference:="ESTIMATE"

because you had no information about the reference.
 

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Thanks for the reply rlvo1. I have other subs in the same module ,perhaps they are some causing bugs . I will have to investigate further.
 

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
rlv01 .I am really new to coding. I didn't mention that I am using a mac with this code. I think that might be part of the problem.

It seems that when I create the pdf first then its like it has changed the printer default to pdf printer of some sort and It no longer finds the original printer.

I have tried the code

MsgBox ActivePrinter

to find the name of the active printer . So I can reset the printer with Application.ActivePrinter after running the pdf macro,but it returns:

unknown printer (check your Control Panel)


Is there a way to set the active printer on mac.

I have seen this exact issue in other forums but could not find a solution.

Thanks.
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi,

Can't help with MAC VBA for that but is this any use?

Both Windows 10 and Apple OS have settings to allow the default printer to be the last used. You can change settings it so it doesn't do that.
However I don't know if that will stop the export to pdf working properly!


 

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Hi,

Can't help with MAC VBA for that but is this any use?

Both Windows 10 and Apple OS have settings to allow the default printer to be the last used. You can change settings it so it doesn't do that.
However I don't know if that will stop the export to pdf working properly!



Thanks for the reply daverunt. I looked in my settings and default is not set to use last printer used its on the onwe i want to use. So that's not it.
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

It was worth a shot.

Have you tried setting up the original default printer then running the Application.ActivePrinter to get its name so you then know what to reset it to?
 

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
It was worth a shot.

Have you tried setting up the original default printer then running the Application.ActivePrinter to get its name so you then know what to reset it to?
Yes I have tried that but it returns "unknown printer (check your Control Panel)"
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
798
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you save your current (working) printer before you run your SaveRangeAsPDF macro, then restore it after?
VBA Code:
Sub PrinterTest()
    Dim CurrentPrinter As String

    CurrentPrinter = Application.ActivePrinter        'save current active printer
    MsgBox "Current Printer:" & vbCr & vbCr & Application.ActivePrinter, vbOKOnly, "Active Printer"

    Call SaveRangeAsPDF

    MsgBox "Current Printer:" & vbCr & vbCr & Application.ActivePrinter, vbOKOnly, "Active Printer"    'see what the active printer is after running SaveRangeAsPDF

    Application.ActivePrinter = CurrentPrinter        'restore active printer to saved value
    MsgBox "Current Printer:" & vbCr & vbCr & Application.ActivePrinter, vbOKOnly, "Active Printer"
End Sub
 

cocobeans

New Member
Joined
Jul 8, 2020
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Hello,
Thanks but that returns the same message:
"unknown printer (check your Control Panel)" three times and the problem continues.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,154
Latest member
gm_jagath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top