Perhaps something like this?
First of all make a copy of all you original files and put them in a special folder for you to test this macro on!!!!
So you need to change the string "sPath" to suit your needs.
Macro starts by looping through all values in column B sheet1, it compaires the value B2:C2 to the value B3:C3, if these are equal then macro just tests net pair of values. When B3:C3 are compaired to B4:C4 these are differnt (C3 is different to C4) so the values B3:C3 gets copied to sheet2. After all different values are found sheet2 gets activated and macro adds values from B column with C colum and add the ".xlsm" exstention in the D column in order to build a string. The first one being "Grade 7 B.xlsm" So based on data in B and C column all file names are build.
Then a range name "Info" is created, sheet1 activated and an autofiter set up with value from column B as first criteria and C as second. The filterd result is copied. The Offset setting should copy from 1 row below heading and 5 rows to left i.e. column E. This is based on your comments that the files to update should first get rid of old data range E4:X64. But looking at the "Grade 7 B.xlsm" file it seems to me that data starts at column E row 14??
After opening the workbook and sheet "Marks" gets activated, macro clears range E14:X64 and pastes in the new data to range E14 and saves and closes workbook. Then "Thisworkbook" (i.e. "AllStudentsMaster") get activated and the next values in "Info" range gets fed into the autofilter, the nex file opened and so forth...
Having gone through all data in "Info" sheet2 gets activated, data cleared and range name "Info" deleted, sheet1 activated and original autofilter setting reset.
Alf
Bookmarks