file split macro help

hello-

i am being asked to split a file by supervisor name. that is to say, every time a supv's name changes in that column (column B in attached file), create a new workbook, copy and paste header rows and employee data for only that supv in this new workbook and name it after the supv's name + "salary planning". however, there is a summary table above the data set that is throwing off my ability to get this done. this summary table needs to be included in the new workbook as well. i am requesting help with this, please.

i am also attaching the vba script i normally use for splitting files as a start, in case it is helpful. thank you in advance.

fb

THANK YOU for your reply.

we're close, just a couple of hiccups. it is not giving each file a unique name. it is naming every file " Salary Planning File.xlsx", so i am getting a "this file already exists in this location, do you want to replace it?" msg when running the macro. files should be named after supv names, so "Mick Jagger Salary Planning File.xlsx", for example.

lastly, the formulas in the summary table up top are all being returned with a #REF error after the macro is run. any tips on how to have that not happen? thank you again.

should i delete this line of code?:
ActiveWorkbook.SaveAs Filename:="C:\Forum Help\Mr2017\" & key & " Salary Planning File.xlsx", FileFormat:=51

i think the formulas issue has been solved. i believe the file name issue still exists. still getting the "this file already exists in this location, do you want to replace it?" error with each file being named " Salary Planning File.xlsx".

works perfectly now. thank you so much for your time. immensely appreciated.

sorry, had one more question. the new files that are created after the macro has run still shows borders around the rows that are no longer populated. for example, there are 6 rows of employee data total in the file. after the macro has run, the "mick jagger" file is comprised of only 2 rows. however, the 4 unpopulated rows after that still show borders around all of the cells. are you able to add a line of code to remove those borders or simply delete the unpopulated rows? thank you so much.

perfection! thank you so much for everything.