vba - Excel - Copy contents of a many cells to multiple worksheets based on the name of the activesheet -

in sheet1 there is, among other things, list of students student id , grade level. in workbook there separate worksheet each student. worksheets named according student id. need copy grade level each student specific worksheet. must done students.

for example column aa contains student id's, column ab contains grade levels each student. need copy student 12345, grade 4 worksheet 12345 cell f1. need move next student , same thing until have no more students.

i have tried many methods, keep getting stuck. i've found examples close, miss 1 key thing make work i'm hoping able me started. feel should easy, hasn't proven be.

edit:

i looking @ trying figure out in steps. on simple test file tried:

dim long  = 1 sheets.count worksheets(i).activate  workbooks("studata.xlsm").sheets(i).range("f1").value = workbooks("studata.xlsm").sheets("sheet1").cells(i, 2) next 

then tried:

dim sheetname string  sub activatesheet(sheetname string)      worksheets(sheetname).activate end sub  

you don't need vba this.

first create following named formula (ctrl+f3->new) sheetname in name: field scoped workbook. enter following exactly below in refers to: , click ok:

=right(cell("filename",!$a$1),len(cell("filename",!$a$1))-find("]",cell("filename",!$a$1),1))

be sure include ! @ beginning of each cell address! important. quick explanation: although scoped entire workbook, ! @ beginning of cell addresses makes each cell address in named formula evaluated in context of current worksheet. equivalent having different sheetname variable each worksheet (scoped each worksheet).

now select first student sheet. press ctrl+shift+pgdn , repeat, or hold ctrl , click each individual student sheet, until student sheets selected. editing of student sheets @ same time.

in cell f1 of 1 of student sheets (doesn't matter which), enter following:

=index('all students sheet'!$ab:$ab,match(value(sheetname),'all students sheet'!$aa:$aa,0))

(of course you'll need replace all students sheet name of first sheet.)

finally, deselect multiple sheets selecting first sheet can continue working without editing sheets @ same time.

edit: note in order sheetname work expected, workbook must have been saved disk (i.e. not work on new workbook until saved since cell("filename",<cell address>) formula needs filename).