How to fill in a form and the data to be pull into another workbook and pv this summary

[ec4excel]

Hoped the experts in this forum could help.

I had tried to write my 1st VBA but seems like cannot work.

I had created a form for my users to use to submit their ordering of stationery.

After they had filled up the fields, I will then copy these into a master list, in a separate file and named the worksheet as "Summary".

Option A: I do not wants to automate the copying, but uses a Command Button named "Update" and only updates when I opened this file from User A, User B, User C.

Option B: I allows user to automatically submit it to my Summary so that I don't have to open the file one by one.

Can someone help with these two scenairos?

After receiving data from all users, I will need to do a sum for the usage for the month and these data will be then put to a pivot table for easy viewing.

Read online that for the summary worksheet with VBA, it is not advisable to insert as a table. Is this true?

If yes, can I then copy the data from this worksheet Summary to another workbook call PVSum and do the monthly summary automatically? And draw lines at the same time??

[john55]

Hi,
it's for option1, replace yr code with this one, see the modifications.

Please Login or Register  to view this content.

[ec4excel]

Dear John55,

Thanks for the correction. I am having problem with my land line to log in, thus, using mobile data line to access into here. Even though can only help on submitting the data, but is good enough. Hoped that some other good souls can help on the auto adding (closing) for each month. Else, will need to do it manually by copying the data out into another worksheet.

Once again, thanks for your help!! It's great!

[john55]

Hi,
Thank you for the rep! Glad it help you for a start.
if you want you can add two buttons in Summary in order to do sum

Please Login or Register  to view this content.

and to clear

Please Login or Register  to view this content.

[ec4excel]

Dear John55,

Thank you for the sum code. It works. But if I already have lots of data in all months from Jan till Dec, where and how can I insert a command to "judge" and sum according to the months? For example, in my sample worksheet, I stopped at Mar, what if we already have data till Dec 13, and needs to insert a sum for each month, can help to do this?

I had tried to insert rows after each month, but the codes that you had written above will still sum till the last row of the data. It will not find and sum according to the months even when I inserted a blank row in between.

[john55]

Hi, there are several ways to accomplish this. I am sorry...

[ec4excel]

Dear John,

Thanks for replying. Worst come to worst, click a button to do it monthly.