Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vba to copy data from sale receipt to next available column in an inventory worksheet
Hello,
Here's what I have: 1. I have an excel sale receipt worksheet which draws data from an excel data worksheet in the same workbook. The sale receipt macro is running perfectly, and is set up to allow for different versions of excel, while doing the following: save as a suggested filename based upon a cell's contents, while allowing the user to specify a directory, and then close the program. 2. I have an excel worksheet set up as a running inventory, with columns for each new order, while there is one row for each of our 51 products. Right now I am having to manually enter each new order in the next available column, manually denoting the quantity in each row's cell that corresponds with the new order's column. The inventory then adds all of the row to learn the total outgoing product, and subtracts it from data of the available product (which was compiled by the same manner in another worksheet for incoming product). So, what I am needing: I am needing to append the script on my receipt so that it will automatically save data (product and quantity) from the excel sale receipt worksheet into the inventory worksheet in the next available column. This will result in a copy of my customer receipts being saved for each transaction (already happening), as well as the inventory being automatically adjusted each time a receipt is saved. I'm guessing the vb script addition to what I have will be moderately straight forward, but I have NO idea where to start with it. In my sale receipt (the Worksheet is named 'Receipt'), the item number (starting at row 9) is in column B, and the quantity is in column A. In my inventory worksheet (named as 'M.A. Sales to Customers), the first order in is column D, with the date in D1, Customer Name in D2, and the first product in D7, with the rest of the products being in D8, D9, D10, etc. I am using Office 2003, but would like to continue my present trek of making the script user friendly for any version. Below is my present script. Is there anyone who can rewrite/append this? Thanks in advance - I am totally at a loss! Sub SaveAsNewFileAndClose() Dim wb As Workbook Dim NewFileName As String Dim NewFileFilter As String Dim myTitle As String Dim FileSaveName As Variant Dim NewFileFormat As Long Set wb = ThisWorkbook If Application.Version = 12 Then NewFileName = wb.Sheets("Receipt").Range("H6").Value & ".xlsm" NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm" NewFileFormat = 52 Else NewFileName = wb.Sheets("Receipt").Range("H6").Value & ".xls" NewFileFilter = "Microsoft Excel Workbook (*.xls), *.xls" NewFileFormat = xlNormal End If myTitle = "Navigate to the required folder" FileSaveName = Application.GetSaveAsFilename _ (InitialFileName:=NewFileName, _ FileFilter:=NewFileFilter, _ Title:=myTitle) If Not FileSaveName = False Then wb.SaveAs Filename:=FileSaveName, _ FileFormat:=NewFileFormat Else MsgBox "File NOT Saved. User cancelled the Save." End If Application.Quit End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a receipt or invoice so that the receipt Nos. change automatically | Excel Discussion (Misc queries) | |||
linking a sales receipt worksheet to an inventory worksheet | Excel Worksheet Functions | |||
lookup inventory to a receipt | Excel Discussion (Misc queries) | |||
Copy Column data to other worksheet | Excel Programming | |||
is there a free point of sale and inventory template available? | Excel Discussion (Misc queries) |