I have a spreadsheet that I have created for our customer service dept. that will automatically generate a page that is printed on our shipping and receiving dept. as well as place pertinent information on a traffic spreadsheet for tracking purposes.
I seemed to get everything working pretty well and wanted to move it to another co-workers computer for them to test. It seems that the Network printer in shipping and receiving wasn't recognized as Ne12:, so the first thing I need to do is somehow search to find out where the printer \\traffic\Traffic is located. I saw a code somewhere else that seemed to do this, but it locked my excel up as soon as I closed VB.
The other thing that I have noticed is that if the file "traffic.xlsm" isn't opened on the local machine, the code errors out. Can someone explain how to check to see if that sheet is opened and if not, open it before writing.
Thank you all for your help thus far and I am confident that someone can come up with a solution to my problem rather easily.
Private Sub CommandButton1_Click()
'populate top of Shipping sheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Set ws1 = Sheets("Cover")
Set ws2 = Sheets("Quote")
Set ws3 = Sheets("Order Form")
Set ws4 = Sheets("Shipping")
Application.ScreenUpdating = False
ws4.Visible = True
ws4.Range("A3:V500").Clear
ws4.Range("A3:W3").HorizontalAlignment = xlCenter
ws4.Range("A3") = ws1.Range("A1") 'Need By Date
ws4.Range("B3") = ws2.Range("C6") 'opportunity name
ws4.Range("C3") = ws3.Range("G4") 'original order date
ws4.Range("C3").NumberFormat = "mm/dd/yyy"
ws4.Range("D3") = Left(ws2.Range("G12"), 2) 'insert vendor initials
ws4.Range("E3") = ws2.Range("C11") 'insert rep
'received
ws4.Range("H3") = ws2.Range("A503") '# parts
ws4.Range("I3") = ws1.Range("C48") 'Drop
ws4.Range("J3") = ws1.Range("C49") 'P/U
ws4.Range("K3") = ws1.Range("C50") 'Flat
ws4.Range("L3") = ws1.Range("C51") 'Assembly
ws4.Range("N3") = ws1.Range("C52") 'Mods
ws4.Range("O3") = ws1.Range("C53") 'Delivery
ws4.Range("Q3") = ws1.Range("C54") 'Installation
ws4.Range("S3") = ws3.Range("Z5") 'Balance Due
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'create Shipping sheet
ws2.Range("A6:F10").Copy Destination:=ws4.Range("A4:F8")
ws2.Range("G13").Copy Destination:=ws4.Range("G8")
ws2.Activate
ActiveSheet.Unprotect
'ws4.Range("C9:G500").UnMerge
'copy parts from Order Form
ws3.Range("A14:B500").Copy Destination:=ws4.Range("A9")
ws3.Range("E14:H500").Copy Destination:=ws4.Range("C9")
ws4.Range("H6:J8").Merge
'vendor initial on Shipping sheet
ws4.Range("H6") = Left(ws2.Range("G12"), 2)
ws4.Range("H6").Font.Size = 20
ws4.Range("H6").HorizontalAlignment = xlCenter
ws4.Range("B9").Value = "PARTS"
ws4.Range("A9") = ws4.Range("H3")
ws4.Range("A3:W3").HorizontalAlignment = xlCenter
ws4.Range("G4:J5").Merge
ws4.Range("G4").Value = "TRAFFIC"
ws4.Range("G4").Font.Size = 20
ws4.Range("G4").HorizontalAlignment = xlCenter
ws4.Range("H9").HorizontalAlignment = xlCenter
ws4.Range("H10").HorizontalAlignment = xlCenter
'Balance Due
ws4.Range("H9:J9").Merge
ws4.Range("H10:J12").Merge
ws4.Range("H9").Value = "Balance"
ws4.Range("H10") = ws4.Range("S3")
ws4.Range("C9:G9").Merge
ws4.Range("H10").Font.Size = 20
'cell outlines
ws4.Range("A57:J58").Borders.Weight = xlThin
ws4.Range("A9:G9").Borders.Weight = xlThin
ws4.Range("A3:W3").Borders.Weight = xlThin
ws4.Range("G8").Borders.Weight = xlThin
ws4.Range("H6:J8").Borders.Weight = xlThin
ws4.Range("G4:J5").Borders.Weight = xlThin
ws4.Range("H9:J12").Borders.Weight = xlThin
ws4.Range("F16:J16").Merge 'signature line
ws4.Range("F16:J16").Borders(xlEdgeBottom).LineStyle = xlContinuous
ws4.Range("F17:J17").Merge
ws4.Range("F17").HorizontalAlignment = xlCenter
ws4.Range("F17").Value = "Customer Signature"
'bottom of page population
ws4.Range("A57:H58").HorizontalAlignment = xlCenter
ws4.Range("A57").Value = "DROP"
ws4.Range("A58") = ws4.Range("I3")
ws4.Range("B57").Value = "PICK UP"
ws4.Range("B58") = ws4.Range("J3")
ws4.Range("C57").Value = "FLAT"
ws4.Range("C58") = ws4.Range("K3")
ws4.Range("D57").Value = "ASSY"
ws4.Range("D58") = ws4.Range("L3")
ws4.Range("E57:F57").MergeCells = True
ws4.Range("E58:F58").MergeCells = True
ws4.Range("E57").Value = "DELIVERY"
ws4.Range("E58") = ws4.Range("O3")
ws4.Range("G57").Value = "MODIFICATIONS"
ws4.Range("G58") = ws4.Range("N3")
ws4.Range("H57:J57").MergeCells = True
ws4.Range("H58:J58").MergeCells = True
ws4.Range("H57").Value = "INSTALLATION"
ws4.Range("H58") = ws4.Range("Q3")
ws4.Range("A59:C59").Merge
ws4.Range("A59").Font.Size = 8
ws4.Range("A59").Value = "Shipping_CAB-NET_US_REV_5-19-2011"
'print to Shipping printer
Application.ActivePrinter = "\\traffic\Traffic on Ne12:"
ws4.PageSetup.PrintArea = "$A$4:$J$58"
ws4.PrintOut Copies:=1
'Write to Traffic sheet
ws4.Select
ws4.Range("A3:W3").Select
Selection.Copy
Windows("traffic.xlsm").Activate
Sheets("traffic").Range("A65536:W65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
ActiveWorkbook.Save
ws4.Visible = False
Application.ScreenUpdating = True
End Sub
Bookmarks