Formula or macro for copying list of cells to worksheets

hello,

i haven't been able find solution i'm looking in searching.

here's best description of i'm trying do:

on sheet1, have list of names in column a.

bob

mary

suzanne

greg

etc...

up 50 total names.

i copy each name same reference cell on it's own worksheet.

sheet1 a1 fill sheet2 c9

sheet1 a2 fill sheet3 c9

sheet1 a3 fill sheet4 c9

and on , forth. can show me formula or macro automate task?

thank you.

a possible formula solution - workbook must saved first.

select sheet2.

hold down shift , select sheetn (e.g. sheet50). group sheets formula type next reproduced on sheets.

in sheet2 cell c9 enter formula:

=indirect("sheet1!a"&sheet(mid(cell("filename",a1),find("]",cell("filename",a1))+1,255))-1)

right click sheet2 tab , select 'ungroup sheets'

Office / Excel / Windows 10 / Office 2016

Cororate