Copy Sheet to New Workbook with VBA

Copying data to a new workbook, saving it and moving to the next item in the list and doing the same thing is not too arduous a task - unless you have a great many files to create.  When this is the case the procedure can be time consuming.  Let's have a look at how VBA can help with this task.  
 
With the following Excel example I will take each item in a list, isolate the data related to that item and create a new workbook with only the data related to that item.  For this Excel article I have cut down one of the Dashboards to use as a demonstration file.

The following YouTube video on my channel outlines the process of saving files to a directory with Excel VBA.

Start File: Save to File.xlsm

The below data can be split into 4 different files. One Excel file for each region that we wish to save down to a directory. There is a drop down in cell D4 that needs to get changed based on a list. The code will loop through the list and change the name of the city and save the file down to the directory before moving to the next city and doing the same thing. The VBA changes those D4 each time which happens automatically. The exampel shows how 4 files can be saved to a directory but you could save 40 or whatever number of files you choose using the same Excel VBA method.

The following Excel workbook contains the above procedure.

Save to File.xlsm