Z
Zowieerog
Hello,
Here's what I have:
1. I have an excel sale receipt worksheet which draws data from an exce
data worksheet in the same workbook. The sale receipt macro is runnin
perfectly, and is set up to allow for different versions of excel, whil
doing the following: save as a suggested filename based upon a cell'
contents, while allowing the user to specify a directory, and then clos
the program.
2. I have an excel worksheet set up as a running inventory, with column
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 nex
available column, manually denoting the quantity in each row's cell tha
corresponds with the new order's column. The inventory then adds all o
the row to learn the total outgoing product, and subtracts it from dat
of the available product (which was compiled by the same manner i
another worksheet for incoming product).
So, what I am needing:
I am needing to append the script on my receipt so that it wil
automatically save data (product and quantity) from the excel sal
receipt worksheet into the inventory worksheet in the next availabl
column. This will result in a copy of my customer receipts being save
for each transaction (already happening), as well as the inventory bein
automatically adjusted each time a receipt is saved.
I'm guessing the vb script addition to what I have will be moderatel
straight forward, but I have NO idea where to start with it.
In my sale receipt (the Worksheet is named 'Receipt'), the item numbe
(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 firs
order in is column D, with the date in D1, Customer Name in D2, and th
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 o
making the script user friendly for any version.
Below is my present script. Is there anyone who can rewrite/appen
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 Su
Here's what I have:
1. I have an excel sale receipt worksheet which draws data from an exce
data worksheet in the same workbook. The sale receipt macro is runnin
perfectly, and is set up to allow for different versions of excel, whil
doing the following: save as a suggested filename based upon a cell'
contents, while allowing the user to specify a directory, and then clos
the program.
2. I have an excel worksheet set up as a running inventory, with column
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 nex
available column, manually denoting the quantity in each row's cell tha
corresponds with the new order's column. The inventory then adds all o
the row to learn the total outgoing product, and subtracts it from dat
of the available product (which was compiled by the same manner i
another worksheet for incoming product).
So, what I am needing:
I am needing to append the script on my receipt so that it wil
automatically save data (product and quantity) from the excel sal
receipt worksheet into the inventory worksheet in the next availabl
column. This will result in a copy of my customer receipts being save
for each transaction (already happening), as well as the inventory bein
automatically adjusted each time a receipt is saved.
I'm guessing the vb script addition to what I have will be moderatel
straight forward, but I have NO idea where to start with it.
In my sale receipt (the Worksheet is named 'Receipt'), the item numbe
(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 firs
order in is column D, with the date in D1, Customer Name in D2, and th
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 o
making the script user friendly for any version.
Below is my present script. Is there anyone who can rewrite/appen
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 Su