Save copy of file in folder hierarchy for each dropdown value

I have an Excel sheet designed to spit out payroll details. The numbers on the sheet are driven by a data validation dropdown in A2, which fills in B2:G2 with identifying information (Last, First, Region, Hospital, PayPeriod, Year) pulled from a data tab.

What I'd like to do is have a macro save a copy of the sheet for each choice in the dropdown into a specific folder in a hierarchy based on the info in B2:G2.

For instance,

Please Login or Register  to view this content.

I'd like that to save a sheet named "2016_PP1_DC_VN_Smith_Scott.xlsx" in the folder Root\2016\PP1\DC\VN\.

And then change to

Please Login or Register  to view this content.

And save the sheet "2015_PP3_NY_RV_Jones_Karen.xlsx" in the folder Root\2015\PP3\NY\RV\.

I have a macro that's part of the way there. It goes through each drop down and saves the file with the correct filename (Though it's renaming the initial file) I need help adding the functionality to save the sheets in a hierarchy of folders and not overwrite the original document with the most recent saved sheet name.

Totally fine with continuing to use this macro with edits or start from scratch.

Please Login or Register  to view this content.

Thank you guys for the help! If you're feeling verbose, it would be great to have some details in your response so I can learn.

My suggestions. I've added a second macro "MakeFolders" that is used to insure the folders exist in the created RelativePath string.

Be sure to EDIT the start the RelativePath so it begins in the correct place. I set it to "C:\Root\"

Please Login or Register  to view this content.