Hello Ozgrid Forum!
I am needing someone to show me how to build a VBA code to do all the things I want to do with the attached file (it is false info but will suffice for VBA test needs and to illustrate what I need accomplished). What I need to be able to do is have a VBA code that will:
1) Split the one TEST Booklet.xlsx worksheet('Sheet1') into multiple WorkBOOKS with specific file saved names in .xlsx format. I need the VBA to look down Column C (Department) and create a new WORKBOOK.xlsx for each unique list of Departments. Meaning that if the Department repeats then it needs to be on the same workbook and when the department changes to a different number, then a new workbook is created listing those departments. I need the header row to remain for each newly created file and in the same format as the original. Also, all corresponding row data needs to be repeated into the newly created workbooks (I don't want to end up with just a list of departments but all the data tied to each department (e.g., Market, Group, Location, Employee Number, etc...))
2) When the new workbook is created, I need the file saved to "C:\Workbooks" and with a specific file name for each. The file name needs to be Group-Department (example: Row 1 in my attached test file would need to be saved as 11-1103.xlsx. Row 4 should trigger a new workbook creation with a file save name of 12-1204.xlsx)
3) Once all the files are created and saved as the proper name in the "C:\Workbooks" folder, I need the VBA code to move (Not copy) the files to their final spot based on their Group. Meaning I will have a C:\Workbooks\11 folder, a C:\Workbooks\12 folder, a C:\Workbooks\13 folder, etc... I need the newly created files in the C:\Workbooks folder to be moved into their respective Group folders. So file name 11-1103.xlsx would need to be moved from C:\Workbooks into C:\Workbooks\11 and file name 12-1204.xlsx would need to be moved from C:\Workbooks into C:\Workbooks\12 folder. Make sense?
I know this is asking a lot without actually showing any work I've done to try to figure this out on my own but I honestly don't know where to begin on this one. If anyone can use the attached test document and make a macro accomplishing my needs, I can adjust it to work with my real data.
Thanks again for anyone willing to help out!!!