This should work
Dim CurrentPrinter As String
CurrentPrinter = Application.ActivePrinter
If Application.ActivePrinter <> "NPI140D8A (HP LaserJet 400 M401n) on Ne00:" Then _
Application.ActivePrinter = "NPI140D8A (HP LaserJet 400 M401n) on Ne00:"
wsO.PrintOut ''/// use PrintPreview if you want to view before printing
Application.ActivePrinter = CurrentPrinterYour code has a comma in it. It should be just ws0.Printout
Roy,
I plugged in what you sent and I still get the same run time error. I read where the port number (Ne00 in this case) will change from Ne00 thru Ne16. I verified that the printer I am testing with still had Ne00 before I tried the code.
I will keep trying. Thank you!!
Which line is highlighted when the error occurs?
It's the "Application.ActivePrinter" line right before the wsO.printout.
Sub Submit()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim path As String
Dim filename1 As String
Dim CurrentPrinter As String
CurrentPrinter = Application.ActivePrinter
Application.ScreenUpdating = False
'~~> Source/Input Workbook
Set wbI = ThisWorkbook
'~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Sheet1")
path = "y:\misc docs\"
filename1 = wsI.Range("e7").Text
'~~> Destination/Output Workbook
Set wbO = Workbooks.Add
With wbO
'~~> Set the relevant sheet to where you want to paste
''/// this will actually be the activesheet
Set wsO = wbO.Sheets("Sheet1")
'~~>. Save the file
'51- xlsx no macros
'52- xlsm with macros
'56-xls
.SaveAs Filename:=path & filename1 & Format(Now(), "MMDDYY hhmmss"), FileFormat:=51
'.SaveAs Filename:="y:\misc docs\reqtest3.xlsx", FileFormat:=xlOpenXMLWorkbook
'~~> Copy the range
wsI.Range("A1:I42").Copy
'~~> Paste it in say Cell A1. Change as applicable
wsO.Range("A1").PasteSpecial Paste:=xlPasteValues
wsO.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
If Application.ActivePrinter <> "NPI140D8A (HP LaserJet 400 M401n) on Ne00:" Then _
Application.ActivePrinter = "NPI140D8A (HP LaserJet 400 M401n) on Ne00:"
wsO.PrintOut
Application.ActivePrinter = CurrentPrinter
wbO.Close Savechanges:=True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
If Err = 0 Then MsgBox "Requisition Submitted", vbInformation
End SubThat is exactly as it is in my code module. "Then" is in blue while all the other text is in black.
Same error, same line.
Hey Roy,
I think I have found a temporary solution. I didn't and still don't really want the user to have to do anything but fill in the blanks and hit submit but..... I made the changes below . We will have to make sure the printer we want to use is set up on the users kiosks if we end up using this until the Application.ActivePrinter is figured out. I have only tested it on my computer so far but will try it on one of the kiosks today. I appreciate your help and will keep trying to get the original plan to work in the meantime.
Hi Roy,
Still no luck with the "Application.ActivePrinter" portion of the code. I will keep trying but wanted to drop back in to say Thank You again for all your help.
I appreciate your time and guidance.
Best Regards,
Tim
I'll take a look as soon as I can. Just busy upgrading my web site. Remind me tomorrow if necessary
Don’t have an account yet? Register yourself now and be a part of our community!