Saving Data From An Order Form

Posted by L¥NX on December 11, 2001 6:55 AM

Hey guys,,

I have a small problem that I need some help to solve, on one sheet of my workbook i have an order form where the operator inputs what product the custoemr wants to buy and another sheet with a invoice on, the invoice automatically picks up information off the first sheet to print the invoice but;

in another sheet i need to be able to record all the days transactions, this is only a very breif discription of each transaction but minimum details include, customer number, name, date, time and the order amount,

the best idea that i have is to; have a button on the first sheet that starts a recording process that inputs the data needed into the other sheet, if this is at all possible then please tell me how to do it,,

any other assistance with this matter would be greatly apreciated,

thanks for you time,

L¥NX

Posted by Andy Gee on December 11, 2001 9:10 AM

A simple macro should do the trick,
Create (if needed) a sheet for your transaction summary. Say "customer number", "name", "date", "time" and "order amount" are in cells A2, B2, C2, D2 and E2. (Put something in a1,b1,c1,d1 and e1, it doesn't matter what!)

Select the sheet from which you wish to activate the macro then,
Goto TOOLS, MACRO, RECORD NEW MACRO.
Give it a name and click OK.

A box with two buttons should appear, the blue square is the stop recording button and the other is for relative references. Make sure the relative refs button is pressed in.

1. Press Control+G (goto), a goto box will appear, type in the cell reference for your customer name (ie H5). The selection box should surround your customer name. Press Control+C (copy). Select the sheet with your summary data, Press Control+G and type A1. Now hold down the control key (CTRL) and press the down arrow. Relese the control key and press the down arrow again. Press Control+V (Paste). Select the first sheet and restart the process from step one substituting the Control+G (goto) values for the relevant cell references until all values have been copied across to the summary sheet.

When this has been achieved stop the macro recorder and create a button on your main sheet to activate the macro.

To create a button, right click anywhere on the menubar and select forms from the list. Mouse over the new buttons until you see the word "Button" appear next to the mouse pointer. Click it and drag out a button on the sheet. Select the macro name from the list and click OK.

Good luck.