R
Rich Kniatt
I have this PO system I have been working on for the last few weeks,
Ive got the Salesman part down, now Ive run into a proble with the
Receiving side.
Baisicly when our receiver gets the material if order is complete he
hits a button and it saves a copy with -RC behind the name, this works.
If there is a backorder I need to save a copy of what came in (havent
started this yet) and an additional copy with the Qty Backordered in
the Qty Ordered column with a -1 behind the name.
Ive been going on this so long Im sure its all messed up and any help
would be appreciated. This code runs off a Command button on the
worksheet, that way it stays with the copy.
copy of code: (im new at this so its messy)
Private Sub CommandButton8_Click()
ScreenUpdating = False
Dim Msg, Style, Title, Response, MyString
Msg = "Are you sure you want to complete PO and/or Generate B/O PO?"
' Define message.
Style = vbYesNo ' Define buttons.
Title = "Complete PO" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
mypath = ActiveWorkbook.Path
If Range("BOQty") = 0 Then
Sheet1.CommandButton5.Visible = False
Sheet1.CommandButton8.Visible = False
Range("a1").Select
filesavename =
Application.GetSaveAsFilename(ActiveSheet.Range("b3") & "-RC" &
".xls")
ActiveWorkbook.SaveCopyAs filesavename
Sheet1.CommandButton5.Visible = True
Sheet1.CommandButton8.Visible = True
'saves original PO
ActiveWorkbook.Close (False)
Else
'determines BO Qty
If Range("BOQty") > 0 Then
'select next blank cell
ActiveSheet.Range("o65536").End(xlUp).Offset(1, 0).Select
'Add next PO # -1,-2 ect.
ActiveCell.Value = Range("b3") & "-PO-" & Range("POcount") &
".xls"
'copy sheet with BO
ActiveSheet.Copy
'copy BO Qty to New PO
Range("d15:d34").Copy
Range("b15:b34").PasteSpecial xlPasteValues
Range("c15:c34").Value = ""
Range("d54:d73").Copy
Range("b54:b73").PasteSpecial xlPasteValues
Range("c54:c73").Value = ""
Range("d93:d112").Copy
Range("b93:b112").PasteSpecial xlPasteValues
Range("c93:c112").Value = ""
Range("d132:d151").Copy
Range("b132:b151").PasteSpecial xlPasteValues
Range("c132:c151").Value = ""
Range("d171:d190").Copy
Range("b171:b190").PasteSpecial xlPasteValues
Range("c171:c190").Value = ""
Cells(1).Select
Application.CutCopyMode = False
End If
End If
End If
'Get Save as name
filesavename = Application.GetSaveAsFilename(ActiveSheet.Range("b3") &
"-PO-" & Range("POcount") & ".xls")
If filesavename <> False Then
ActiveWorkbook.SaveAs filesavename
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Else
If filesavename = False Then
ActiveWorkbook.Close (False)
End If
End If
'Else ' User chose No.
'MyString = "No" ' Perform some action.
'MsgBox.Hide
'End If
'Cells(1).Select
'ActiveWorkbook.Close (True)
ScreenUpdating = True
End Sub
Ive got the Salesman part down, now Ive run into a proble with the
Receiving side.
Baisicly when our receiver gets the material if order is complete he
hits a button and it saves a copy with -RC behind the name, this works.
If there is a backorder I need to save a copy of what came in (havent
started this yet) and an additional copy with the Qty Backordered in
the Qty Ordered column with a -1 behind the name.
Ive been going on this so long Im sure its all messed up and any help
would be appreciated. This code runs off a Command button on the
worksheet, that way it stays with the copy.
copy of code: (im new at this so its messy)
Private Sub CommandButton8_Click()
ScreenUpdating = False
Dim Msg, Style, Title, Response, MyString
Msg = "Are you sure you want to complete PO and/or Generate B/O PO?"
' Define message.
Style = vbYesNo ' Define buttons.
Title = "Complete PO" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
mypath = ActiveWorkbook.Path
If Range("BOQty") = 0 Then
Sheet1.CommandButton5.Visible = False
Sheet1.CommandButton8.Visible = False
Range("a1").Select
filesavename =
Application.GetSaveAsFilename(ActiveSheet.Range("b3") & "-RC" &
".xls")
ActiveWorkbook.SaveCopyAs filesavename
Sheet1.CommandButton5.Visible = True
Sheet1.CommandButton8.Visible = True
'saves original PO
ActiveWorkbook.Close (False)
Else
'determines BO Qty
If Range("BOQty") > 0 Then
'select next blank cell
ActiveSheet.Range("o65536").End(xlUp).Offset(1, 0).Select
'Add next PO # -1,-2 ect.
ActiveCell.Value = Range("b3") & "-PO-" & Range("POcount") &
".xls"
'copy sheet with BO
ActiveSheet.Copy
'copy BO Qty to New PO
Range("d15:d34").Copy
Range("b15:b34").PasteSpecial xlPasteValues
Range("c15:c34").Value = ""
Range("d54:d73").Copy
Range("b54:b73").PasteSpecial xlPasteValues
Range("c54:c73").Value = ""
Range("d93:d112").Copy
Range("b93:b112").PasteSpecial xlPasteValues
Range("c93:c112").Value = ""
Range("d132:d151").Copy
Range("b132:b151").PasteSpecial xlPasteValues
Range("c132:c151").Value = ""
Range("d171:d190").Copy
Range("b171:b190").PasteSpecial xlPasteValues
Range("c171:c190").Value = ""
Cells(1).Select
Application.CutCopyMode = False
End If
End If
End If
'Get Save as name
filesavename = Application.GetSaveAsFilename(ActiveSheet.Range("b3") &
"-PO-" & Range("POcount") & ".xls")
If filesavename <> False Then
ActiveWorkbook.SaveAs filesavename
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Else
If filesavename = False Then
ActiveWorkbook.Close (False)
End If
End If
'Else ' User chose No.
'MyString = "No" ' Perform some action.
'MsgBox.Hide
'End If
'Cells(1).Select
'ActiveWorkbook.Close (True)
ScreenUpdating = True
End Sub