randomly entering strings from one worksheet to another

[lbesley]

Hi,

A bit of a sticky one! See attached the excel spreadsheet. I am trying to copy and randomly paste the data from 'new employees' worksheet into the cells denoted 'SPARE' in the 'schedule' worksheet. Is there a VBA code that can help me magically do this? This data given within the attached excel document is only a small (un-confidential) selection of the data I will actually be manipulating in this way. At present I do this manually, a very long and lengthy process... Any help would be greatly appreciated.

cheers!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[lbesley]

Wow that's fantastic! Thanks very much.

Is there a way, however, so that each new employee i.e. 'GOU-FF1' is only entered once into the schedule? Additionally, if there is no "SPARE" cells to copy the new employees too can it bring up a message box to let the user know to create a new training program?

I really really appreciate your help - you are shaving days off my work load!! Love excel!

[lbesley]

Is there a way for it to select any length of data in the 'New Employees' Worksheet? As new employees could be longer than 10 rows of data, the amount is varied each time.

Cheers!

[JOHN H. DAVIS]

It should already account for varying length of new employees. Try:

Please Login or Register  to view this content.

[lbesley]

Thanks very much for your code, its working in that it copies over all employees regardless the number of employees. You are making my life a million times easier.

However, it seems to wipe the SPARE from cells that are SPARE and not filled, then call the message box, even though technically there are still 'SPARE's, see attached the workbook. Is there a way to prevent this from happening? it still appears to duplicate some of the employees. The Before worksheet is how the schedule looked before the macro is run, and the employees to add before is how the employees are entered before the macro is run.

I really appreciate all your help.

Cheers,
Lbesley

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[lbesley]

amazing!! works fantastically - thankyou thankyou thankyou

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9 and mark this thread as solved.