Automatically combine multiple mailing lists into a new worksheet

Hello,

I have a single Excel file that contains all of our mailing list data. Contact info is across multiple 'job title' worksheets (e.g. one for managers, one for executives, etc...). I also have 'mailing list' worksheets that are a combination of the 'job title' worksheets (e.g. a mailing list for Managers and Executives, a mailing list for Support Staff and Managers, etc...).

Currently my workflow is:
1) Throughout the month I regularly update the 'job title' worksheets with new contact information (culled from multiple sources)
2) End of the month I manually copy paste data from various 'job title' worksheets into the relevant 'mailing list' worksheets
3) I upload the 'mailing list' worksheets into our email broadcasting service, so that our lists are updated on a monthly basis

As you can imagine it's a fairly time consuming process and I think I can save tons of time on step #2 with a bit of Excel elbow grease (and the great pool of knowledge here).

I'm hoping there's some way I can automate my 'mailing list' worksheets, so that if data in the 'job title' worksheets changes then the 'mailing list' worksheets will automatically update. E.g. I add John Doe to the Executives worksheet, then John Doe will automatically appear in the 'Managers and Executives' mailing list worksheet.

Can anybody suggest a solution on how I can do this?

I've attached a sample. On my 'MERGED' worksheet I'd like to have Persons 1-6 and their emails appear. If I add a 'Person 7' to 'Sheet2', then they should also automatically appear in the 'MERGED' worksheet.

Macros and coding terrify me, they just don't click in my brain and I've never been able to do them properly. I completely don't mind doing a more complex solution that involves clicking alot of buttons, and tweaking long formulas. I'll also need to be able to modify the solution to suit my various mailing list needs.

Thank you!

Thanks Naveed!

Two things:
- I tried pasting in a set of contacts, only the first contact was moved onto the 'MERGED' sheet
- Is there any way to do this without macros? My skill level is beginner, so formulas I can barely comprehend let alone macros. In the end I'll need to modify the solution and add-on my own customizations (e.g. a 'MERGED2' sheet that grabs contacts from only 'Sheet2' and 'Sheet 3'). I'm pretty sure i'd be able to handle the modifications on my own by editing any formula-based solution.