Need Macro to Fill Database with Form Info

Hello All

I would greatly appreciate some assistance. I am new to Macro's, but eager to learn.

I have attached a form that will be filled out routinely. I am wanting to assign a Macro to the button in the top right corner that will do the following:

1. Print the form.
2. Copy the entries from the form into the "Database" sheet as shown.
3. Blank the grey cells in the "Form" Sheet.
4. Change the value in J3 in the "Form" Sheet from TR0001 to TR0002.

I only now how to make Macro's with the "Record" function, and that won't work with Copy & Paste, so I'm needing assistance to get this figured out.

If any clarrification is needed, please let me know. If the structure I have created is not great for Macros, I am open to suggestions, but I am not the end user of the form, so keeping it close to this would be ideal.

NOTE: The end user will have Excel 2003, so hopefully that won't be an issue.

See if this is what you're looking for.

Very Close Tinbendr!
I was able to open up your code, and edit it, so that instead of the date posting in column B, it posted in Column C, and so forth.
The only issue remaining is with point #4 in my original post:

The value in J3 should change from TR0001 to TR0002, and then to TR0003, etc.....
TR0002 should have got pasted in B4 as well


The attached file shows this better than my explanation.

More a general question, but when I modified the "POST" macro, I closed the macro window, and when I reopen Visual basic, the macro is not there. How can i reopen it to view/edit it?

Oops, sure did skip step 4. Try this.

Not sure why the code disappeared. The worksheet is protected so that when you enter and press Tab, it skips to next cell.

Make sure you are looking in the right module. The button code is in the Sheet1 module.

The posting code is in module1.

Thanks Tinbendr. After some growing pains, I seem to have got it working.
Trying to add item #1, which is to print the form, is causing me issues. I have recording a macro and added it to your posting code, and it works perfectly, but after some testing, it seems it only works in excel 2010.

The red portion below is the code I added. It works in 2010, but not 2003:

Please Login or Register  to view this content.

It seems to get hung up on the "ignore print areas:=False" aspect.

If you have a way to make this work in 2003, that would be MUCH appreciated. Thanks for your help!

Here's a little different code for that.

Please Login or Register  to view this content.

Thanks Tinbendr!
A couple changes, and everything seems to be working perfectly. Thanks so much!

Here is the final version I ended up with:

Please Login or Register  to view this content.

So now that everything is working, of course the end user is requesting something different!
They are wanting multiple people to be able to post to the database. The only way I can think to do this, is to have two different files in the same folder:
1. The form that multiple people can have open at the same time and can create entries.
2. The database file that is password protected, and virtually always a "Read-Only" file is accessed, which houses all of the entries that are being "posted".

Does this make sense? The TR0001 aspect will also have to be ignored, as with multiple people using the file, this will not work.
I have no idea how to change the code to handle posting to an unopened workbook. They also requested the file be e-mailed automatically as well.
Would you be able to help again?

I've attached seperate sheets for the "Form" and "Database". The new process would be to

1. Print the document.
2. Email the document to [email protected]
3. Copy and paste entry into columns C:H of the "Database" file.
4. clear entry cells in the "Form" file.

Let me know if this is possible. Thanks again.