Populate more than one worksheet from a user form

**bigpee** · 10-19-2009, 09:27 AM

Hi,

I've only just starting using Excel for anything other than basic calculations and have got a little stuck with a user form. On my attached spreadsheet I am trying to set up a form for staff incident reporting. There is a Contents worksheet and then each incident has it's own detailed worksheet. The user will click on 'create new incident' on the Contents page. This opens a user form. From this form I want to populate the contents worksheet. I then also want it to populate the relevant incident worksheet. I can populate the contents page but I need help getting the correct Incident worksheet populated at the same time. Hopefully all will become clear running the file.

Thanks in advance for any help.

Alan

**royUK** · 10-19-2009, 10:16 AM

Not sure why you need all those identical forms. You are taking the wrong approach.

The data shold be saved from the UserForm to a table of data, ideally with a unique ID number in Column A of the data sheet. You can then quickly fill the sheet that acts as the form by using VLOOKUP

Also, finding the next row in your code by looping is very inefficient. I also, can't see why you use End in the code.

Please Login or Register  to view this content.

**Rick_Stanich** · 10-19-2009, 11:23 AM

Here is a worksheet with a modified macro for your needs.
Note:
I re-ordered the worksheets because they were in reverse order (this always happens when adding worksheets)..

HTH

**bigpee** · 10-20-2009, 03:54 AM

Thank you so much to both the replies. The first one is right in that this does seem a slightly inefficient way of doing things (in that I generate lots of worksheets) but the result is that each worksheet is ready to be printed as a report and has a large area for updates. Given I will have lots of worksheets I will be looking at a more efficient way to order things.

The second reply is spot on - does exactly what I am after. Thank you so much for your help

.

**royUK** · 10-20-2009, 03:58 AM

It would be much simpler to follow my suggestion, then a very simple macro could be used to automate printing a series of forms.

**Rick_Stanich** · 10-20-2009, 09:14 AM

I agree with royUK but I digress it would take me a bit to learn that method than to program it.
Although, if it works....

http://www.excelforum.com/forum-rule...rum-rules.html
10. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.