Create new workbooks based on cell content?

[TheWennerWoman]

I've got what is (to me) quite a daunting task although I am sure the gurus here will find this relatively simple.

I have a workbook with two tabs, Sheet1 and Sheet2.

Sheet1 contains nine columns, columns A-G are populated, columns H & I are for user input. Column A is the important one.

(Sorry, I can't get xl2BB to work otherwise I'd have used that)

PNumber PName CNumber CName ANumber AName Plan
A400000 James C343434 Truss Q456456 Patel Ford
A400009 Price C345578 Jones Q458888 Young Fiat
A400009 Capel C399000 Russi Q458888 Young Fiat

What I would like to do is create a new workbook based on column A. So, in the above example, I would end up with a workbook named A400000 containing one record (and the header row) and a workbook named A400009 containing two records (and the header row).

That's the first part. The second part relates to Sheet2. Sheet2 contains two columns

PNumber AName
A400000 Smith
A400000 Patel
A400000 Jackson
A400000 Roberts
A400009 Young
A400009 Morris
A400009 Peters

In addition to the first part, I'd like to add into the newly created file (on a new sheet) anything from Sheet2 that relates to the file created. So, the file created earlier named A400000 would have a second sheet containing the four records above (Smith, Patel, Jackson, Roberts) and the file created earlier named A400009 would have a second sheet containing the three records above (Young, Morris, Peters).

Any thoughts greatly appreciated as always.

Thank you for reading.

 

[TheWennerWoman]

Thank you so much for your time, I will test this in the morning.

 

[TheWennerWoman]

Thanks once again, all tested and works beautifully.

 

[kevin9999]

Thank you so much for your time, I will test this in the morning.

Happy to help, and thanks for the feedback