Inventory in Excel linked w/ customer invoice

Microsoft Office excel 2007 home & stude...
June 17, 2010 at 08:22:31
Specs: Windows XP
I am trying to do inventory through Excel.  We use Excel to do our customer invoices.  We run into a problem with software that is set up to run inventory because we don't sell everything by cases.  We sometimes open up cases and sell them individually.  I found a way to do it on Excel, but I'm running into an issue.  I use the following code to help us keep our customer invoices organized.
I want to do another close code to take the items that are ordered on worksheet 1 and subtract it from the inventory on worksheet 2. I can link the cells, but when I x it off for it to save as the customer name, it doesn't keep the updated inventory on worksheet 2. This needs to be saved to the original instead of to the sheet that is created upon close. Does that make sense? E-mail me with a sample if needed.

Sub myCloseCode()
'Standard module code, like: Module1.
Dim strDate$, strCustomer$, strFileNm$, strMsg$, myUpDate$

Application.EnableEvents = False
On Error GoTo myErr

'Test for Save option or Exit without saving?
strMsg = "Save this file before closing?"

myUpDate = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Now?")

'Chose "Yes" button!
If myUpDate = 6 Then GoTo mySave

'Chose "No" button!
If myUpDate = 7 Then
Application.EnableEvents = True
Exit Sub
End If

'Build file name!
strDate = DatePart("m", Date) & "-" & _
DatePart("d", Date) & "-" & _
Right(DatePart("yyyy", Date, vbUseSystemDayOfWeek, vbUseSystem), 4)

strCustomer = Sheets("Carolina Fireworks Order Form").Range("C7").Value

strFileNm = "\\Office-pc\public\Customers\" & strCustomer & "-" & strDate & ".xlsm"

'Save current file!
ActiveWorkbook.SaveAs Filename:=strFileNm
Application.EnableEvents = True
Exit Sub

'GoTo Error routine!
Call myErrHandler(Err)
Application.EnableEvents = True
End Sub

See More: Inventory in Excel linked w/ customer invoice

Report •

June 17, 2010 at 08:30:08
Also, the owners of the business won't pay for an inventory software. I'm lucky to have gotten the customer invoices to work in Excel. I'm trying to figure out an inventory system to help the business. We have to pull the order first and then let the customers know what is out instead of knowing ahead of time.

Report •

June 17, 2010 at 08:31:51
Maybe I'm missing something, but why can't you just save the original file before creating the new file?


'Save original


'Build file name!
   strFileNm = "blah blah"

'Save current file!
   ActiveWorkbook.SaveAs Filename:=strFileNm

Report •

June 17, 2010 at 08:51:21
If I save the original file then it will also save the customers order onto the original. I'd then have to open the original again and delete all the info on the sheet before I'd be able to start a new customer invoice.

Report •

Related Solutions

June 17, 2010 at 14:17:38
Using Excel to do order entry and inventory control is terrible.
Use a database at least. A proper program starts for less than
Broken box inventory is another problem. The usual case is to
take the case and make a separate item number.

Report •

June 17, 2010 at 14:35:46
Well, maybe one day when my husband and I take over we can move to something other than Excel. They refuse to change! We have been filling out orders by hand...a $15,000 order by hand takes forever! So I'm lucky to have figured out a way to do it on Excel. However, I think it works just fine for what we need it for. We're not a huge coorperation. That saves time and my arm. I'm sure there is a way to do it on Excel. I'll keep looking. I had it working before we moved to the x'ing it off and saving automatically.

Report •

June 17, 2010 at 14:52:03
You have Excel, do you also have Access?

If you have Access, there are numerous Inventory/Invoice templates on the web, and many are free.

Look here for examples:


Report •

Ask Question