Multiple input forms to Master file

Hi Guys,

I'm new to the forums with a work related question. I was asked to find an elegant method to optimize an internal ordering procedure. Basically, there are several departments who all order products at a central warehouse. Most order daily, some more irregular.

The order forms are uniform, but at the moment they are being filled out by hand and have to be manually put back into Excel for registration purposes. Because users have limited access to order systems, locally placed Excel sheets that link to a master file seems the most viable option right now.

What I want is this:
- a user form where users imput their orders (on this part, I'm good to go) that has an input range (let's say: D5:D42)
- a big red button that says "Order Now" (good to go), which takes all the input values and copies them over to a separate workbook (I assume VBA can handle this. I've looked at several threads around the internet (including a few on this forum), but I'm not experienced enought with VBA to actually translate the code to wat I need from it.).
- a master file that receives all the data from several departments, which can be used for montly/quarterly/yearly analysis. (on this part, I'm also good to go).
* this master file needs to be 'invisible' to users, so without actually opening the file when an order is sent.
- an addition that puts a date stamp (preferrably a Now() function to include the time) into the maste file the moment the order is sent.
- the order form to be 'reset' to blank values after it's been submitted.

I hope I've made my problem clear and any imput is very much appreciated.

The problem as I see it is as follows:

- You make a user form (you have done that)

- On the order form, you ask the user to indicate the Department Name

- After the department fills out the form, they click on the
"Order Now" button which runs a macro/VBA (lets call it "Order" macro)

- The "Order" macro copies the data to a directory (you hard
code the location in the macro)

- While the macro is running, you turn off screen updating
via the macro (the invisibility you want)

- The file captures the date and time and Department Name

- After the data is sent, the macro clears the data from the user form.
I recommend the macro send information back to the user
to indicate that the data was sent. This can be done in the same macro

- Now you need to get the "raw" data into the "master" spreadsheet. I
recommend a separate macro for this controlled by you not the
different departments, in case they messed up.

Hope this helps

Kirk

I think you can do what you want by recording a macro by having Excel memorize the keystrokes.
After you have completed the steps, turn off the macro recorder.
Then you could tweak the code by going into the Visual Basic Code (Alt F11).
Look for a Tab call Module 1 or Module XX

To turn off Screen Updating type this near the beginning of the macro:
Application.ScreenUpdating = False

Turn back screen updating at the end of the macro:
Application.ScreenUpdating = True

You would need a separate macro to input the data into the Master Workbook
Again, you could have Excel memorize your keystrokes

You would need to assign your macro to a button to get it to run as you wished. Do you know how to do that?

Hope than helps

Kirk