R
Roger Farney
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
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