vba to copy data from sale receipt to next available column

February 22, 2012 at 13:38:28
Specs: Windows 7
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


See More: vba to copy data from sale receipt to next available column

Report •

#1
February 22, 2012 at 15:06:16
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.

As for your question, I'm a little confused about a few things.

re: I have an excel worksheet set up as a running inventory

You don't say where this worksheet is. Is it in the same workbook as the Receipt?

re: "...so that it will automatically save data (product and quantity)"

and

re: "...with the date in D1, Customer Name in D2"

Do you want to copy the Date and Customer Name from the receipt also, or just the product and quantity?

But...if the product is already in the inventory spreadsheet, don't you just want the quantity copied to the correct row?

In other words, shouldn't the macro just look up the product in the Inventory Sheet and put the quantity in the corresponding row?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 23, 2012 at 06:09:11
Wow, thank you SO much for replying so quickly. As for the posting method, my sincerest apology, I've never done this before. Thanks.

Your questions have prompted me to realize that I have done a poor job laying out the worksheets. Please allow me a little time to modify them so they are both easier to setup and easier to use. I will post back to this thread when I am more prepared. I assume that you will be notified by the site that the thread has been updated.

Thank you again so very very much!!!


Report •

#3
February 27, 2012 at 13:41:33
Thanks again. I've done some more work on it, so I have a better grasp on what I still need to accomplish - thanks for letting me get back to you!

To review so you don't have to scroll up (and some of that is now innacurate anyway), here's what I'm up to:
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 (nice!) The macro prompts for user input to save the "whole workbook" as a suggested filename (based upon a cell's contents), and also allows the user to specify a directory, and then close the program.

I would actually like to save only the worksheet "Sales Receipt" instead of the entire Workbook. The original "Sales Receipt" worksheet needs to be left unchanged and unsaved so it is ready for the next sale.

My workbook is now called "2012 Ledger", and it contains the following eleven (11) Worksheets:

1. Inventory
2. Incoming Receipt
3. Incoming Summary
4. Sales Receipt
5. Sales Summary
6. Personal Product Receipt
7. Personal Product Summary
8. Mileage
9. Misc Income & Expenses
10. Taxes
11. Add New Product

The end result I am going for, is:

A. To be able to enter data into sheets 2, 4, and 6 (for incoming product, outgoing sales, and personally used product repectively).

B. To have that data put into the next available colum on sheets 3, 5, and 7 respectively. Column A in sheets 3, 5, and 7 contains the product code. Column B has the description of the product. A and B are already in by way of reference to the Worsheet "Inventory" (=Inventory!A4 etc etc.). For each time the "Sales Receipt" is filled in, the cells labeled Date, Subtotal, Taxes, and the amount of each product are entered in the respective cells in the next available column. (You were correct in observing that I don't need the product put in, as it is already in Column 1.)

C. As Worksheets 3, 5, and 7 are filled with data, the contents of those sheets are to be referred into the Worksheet "Inventory", where it then is added to stock, subtracted from stock, and totals given.

D. The Worksheet "Inventory" is also where the product numbers, product descriptions, prices, taxes, COGS, shipping, etc etc are stored.

E. As for the first post I had above, the 'M.A. Sales to Customers' sheet no longer exists, it is now the "Sales Summary". The first order in is column C, with the date in C2, Subtotal in C3, Taxes in C4, and total in C5. Below that, the first product is in C9 and the last product is in C1587.

F. All of the resulting data from Worksheets 1-9 are chanelled into Worksheet 10, which is basically a Schedule C for tax time.

G. Sheet 11 is yet for me to tackle later... it is a form to be used to easily enter new products into the "Inventory" worksheet.

Wow - I can't believe what I've gotten myself into here - easy for you perhaps - but I feel like such a newby!!

Obviously this is easier seen than described, so if you want I can send you the whole Workbook if you like - it would be a lot simpler I imagine. Here is the script I am starting with - posted correctly this time - thanks for the input on how to post code!

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("Sales Receipt").Range("I6").Value & ".xlsm"
       NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
       NewFileFormat = 52
     Else
       NewFileName = wb.Sheets("Sales Receipt").Range("I6").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


Report •
Related Solutions


Ask Question