Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

vba to copy data from sale receipt to next available inventory column

ZowieerogZowieerog Posts: 1Member
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

Sign In or Register to comment.