ADVANCED Print to PDF (with manual name & location) Macro Debug Error

[nerrikjb]

Hi All,

I am a novice when it comes to excel coding. I work in a advertising/sales company as a graphic designer and the previous owner of the company created multiple excel documents so we could send out artwork proof forms to clients. One of the forms he created so we could print to pdf and save it with a custom name and custom location has decided to stop working.

We are still only using Microsoft Excel 2000 by the way.

I click debug and the code comes up as follows and this is WAY beyond my knowledge. There is one section that comes up highlighted in yellow but I don't know what is wrong with it. (I have made it bold below)

Sub Print_Proof()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Red = Range("N2").Value
Print_White = Range("N3").Value
Print_Colour = Range("N4").Value
Fax_Type = Range("N5").Value
Email_Type = Range("N6").Value
' Page_Number = Range("J155").Value

If Range("K2").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_Red
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Else
' stop

End If

If Range("K3").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_White
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Else
' stop

End If

If Range("K4").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_Colour
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Else
' stop

End If

If Range("K5").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Else
' stop

End If

If Range("K6").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Email_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Else
' stop

End If

If Range("L2").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_Red
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=True

Else
' stop

End If

If Range("L3").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_White
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=True

Else
' stop

End If

If Range("L4").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Print_Colour
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=True

Else
' stop

End If

If Range("L5").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=True

Else
' stop

End If

If Range("L6").Value = "True" Then ' Updatexxxxxxxx
' Print Type
Application.ActivePrinter = Email_Type << THIS LINE COMES UP HIGHLIGHTED AS BEING WRONG)
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=True

Else
' stop

End If

Range("B3").Select

End Sub
Sub Fax_Proof_Customer()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("J4").Value
Fax_Type = Range("J5").Value
Email_Type = Range("J6").Value
Page_Number = Range("J155").Value

' Application.ActivePrinter = Print_Type
'ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=Page_Number, Copies:=1, Collate:=True

Range("G2").Select
ActiveCell.FormulaR1C1 = "Check"
'Range("G3").Select
'ActiveCell.FormulaR1C1 = ""
Range("G4").Select
ActiveCell.FormulaR1C1 = "35"
Range("B3").Select

End Sub
Sub Email_Proof_Customer()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("J4").Value
Fax_Type = Range("J5").Value
Email_Type = Range("J6").Value
Page_Number = Range("J155").Value

Application.ActivePrinter = Print_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Application.ActivePrinter = Email_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=3, Copies:=1, Collate:=True

Range("G2").Select
ActiveCell.FormulaR1C1 = "Check"
'Range("G3").Select
'ActiveCell.FormulaR1C1 = ""
Range("G4").Select
ActiveCell.FormulaR1C1 = "35"
Range("B3").Select

End Sub


Sub Print_Proof_External()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("J4").Value
Fax_Type = Range("J5").Value
Email_Type = Range("J6").Value
Page_Number = Range("J155").Value

'Application.ActivePrinter = Print_Type
'ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Application.ActivePrinter = Print_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=Page_Number, Copies:=1, Collate:=True

ConsultantsCopy.Show


Range("G2").Select
ActiveCell.FormulaR1C1 = "Check"
'Range("G3").Select
'ActiveCell.FormulaR1C1 = ""
Range("G4").Select
ActiveCell.FormulaR1C1 = "35"
Range("B3").Select

End Sub
Sub Fax_Proof_External()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("J4").Value
Fax_Type = Range("J5").Value
Email_Type = Range("J6").Value
Page_Number = Range("J155").Value

'Application.ActivePrinter = Print_Type
'ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=Page_Number, Copies:=1, Collate:=True

ConsultantsCopy.Show


Range("G2").Select
ActiveCell.FormulaR1C1 = "Check"
'Range("G3").Select
'ActiveCell.FormulaR1C1 = ""
Range("G4").Select
ActiveCell.FormulaR1C1 = "35"
Range("B3").Select

End Sub
Sub Email_Proof_External()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("J4").Value
Fax_Type = Range("J5").Value
Email_Type = Range("J6").Value
Page_Number = Range("J155").Value

'Application.ActivePrinter = Print_Type
'ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

Application.ActivePrinter = Email_Type
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=Page_Number, Copies:=1, Collate:=True

ConsultantsCopy.Show


Range("G2").Select
ActiveCell.FormulaR1C1 = "Check"
'Range("G3").Select
'ActiveCell.FormulaR1C1 = ""
Range("G4").Select
ActiveCell.FormulaR1C1 = "35"
Range("B3").Select

End Sub
Sub Print_Letter5()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Print_Type
ActiveWindow.SelectedSheets.PrintOut From:=3, To:=3, Copies:=1, Collate:=True


End Sub
Sub Fax_Letter5()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=3, To:=3, Copies:=1, Collate:=True


End Sub
Sub Email_Letter5()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Email_Type
ActiveWindow.SelectedSheets.PrintOut From:=3, To:=3, Copies:=1, Collate:=True

End Sub


Sub Print_Letter()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Print_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate:=True


End Sub
Sub Fax_Letter()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate:=True


End Sub
Sub Email_Letter()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Email_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate:=True

End Sub


Sub Print_Both()
'
' Print_Proof_CC Macro
' Macro recorded 18/12/2001 by Coastal Coasters
'
Print_Type = Range("M7").Value
Fax_Type = Range("M8").Value
Email_Type = Range("M9").Value
Page_Number = Range("M15").Value

Application.ActivePrinter = Fax_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate:=True

Application.ActivePrinter = Print_Type
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate:=True


End Sub


All of the other bits of coding work fine. It just has the error if we try printing to PDF. I can send screen shots of the form I think if need be. If anyone is able to help at all it would be greatly appreciated.

 

[StephenCrump]

Welcome to the Forum!

Your worksheet lists several printers:

Print_Red = Range("N2").Value
Print_White = Range("N3").Value
Print_Colour = Range("N4").Value
Fax_Type = Range("N5").Value
Email_Type = Range("N6").Value

What's in cell N6?

My guess is that this is now an out-of-date printer name, e.g. perhaps you've upgraded this, rather than Excel? .

 

[nerrikjb]

Welcome to the Forum!

Your worksheet lists several printers:

Print_Red = Range("N2").Value
Print_White = Range("N3").Value
Print_Colour = Range("N4").Value
Fax_Type = Range("N5").Value
Email_Type = Range("N6").Value

What's in cell N6?

My guess is that this is now an out-of-date printer name, e.g. perhaps you've upgraded this, rather than Excel? .

Thanks for replying. And it is the PDF printer and the cell says =VLOOKUP($G$2,Consultant_Names!$AA$4:$AH$35,8,FALSE)
We haven't updated any printers but our Adobe Acrobat has been updated but this never seemed to cause a problem in the past
with updates. But its possible the newest version now causes a problem. Any ideas on how to resolve this or correct the coding to suit the new update???

 

[nerrikjb]

It links over to a different spread sheet that has coding in it as well. So much coding everywhere.

 

[StephenCrump]

Try printing manually to the PDF printer from Excel, then run this code:

Sub Test()

    MsgBox Application.ActivePrinter

End Sub

You should find that the value in N6 doesn’t exactly match this ActivePrinter name?

You said that the value in N6 is obtained from the formula: =VLOOKUP($G$2,Consultant_Names!$AA$4:$AH$35,8,FALSE)

That means the incorrect printer name is somewhere in AH4:AH35 in the sheet "Consultant_Names", i.e. depending on the value of G2 in the ActiveSheet. Hopefully this will be a value, and not another formula, in which case you should simply need to update the printer name. The incorrect printer name may be used more than once in this range.