I started making videos to go along with my post on pivot tables called 101 Advanced Pivot Table Tips And Tricks You Need To Know. I wanted to be a bit organized and have all the related files for each video tip in its own folder. This meant creating 101 new folders and then naming them based on each tip in my post.

Doing this manually would be a very tedious task.

1. Copy tip title into clipboard.
2. Open the file explorer and right click to create a new folder.
3. Paste the tip title name into the folder name
4. Prefix the folder name with”001 – ” because I wanted them to be listed in the file explorer in the same order as the post.
5. Repeat steps 1 to 4 a hundred more times 🙁

This is not really the kind of task I find fun, and usually prompts me to think “how can I avoid doing this?“.

Since I already had my tip titles listed in an Excel sheet, this was the perfect task for a little bit of VBA.

Creating the Folders from a List

I set up a named range in my sheet called MakeFolderPath as an input for a folder path. This is where you can put the location you want to create all your folders.

I also set up a 1 column table and named it MakeFolderNames. This table will contain the names of all the folders I want to create.

Creating named ranges and tables for our inputs will just allow us to move these around on the sheet without the need to update a reference in the VBA.

Sub MakeFolders()

Dim Folder As Range
Dim FolderPath As String
    
FolderPath = Range("MakeFolderPath").Value
   
On Error Resume Next
   
For Each Folder In Range("MakeFolderNames[Folder Name]")
    MkDir FolderPath & "\" & Folder.Text
Next Folder

End Sub

It’s a simple bit of VBA. It loops through each item in our list of folder names and creates a folder with that name using the MkDir command. It also skips over any errors that might occur due empty cells in our list, the folder already existing or special characters that aren’t allowed in a folder name.

Deleting Folders from a List

Deleting all folders in a directory isn’t hard, you just highlight them all and press Delete. But if you have a large number of folders and you only want to delete a subset of those, it can be time consuming to delete them. You would need to individually select and delete folders in this case.

If you have a list of the subset of folders, then we can use VBA to delete them in a similar way to how we created folders from a list.

The named range to read the path is called DeleteFolderPath and the table containing my list of folder names to delete is called DeleteFolderNames.

Sub DeleteFolders()

Dim Folder As Range
Dim FolderPath As String
    
FolderPath = Range("DeleteFolderPath").Value
    
On Error Resume Next
    
For Each Folder In Range("DeleteFolderNames[Folder Name]")
    RmDir FolderPath & "\" & Folder.Text
Next Folder

End Sub

This will loop through and delete any folders in my list using the RmDir command. It will skip any errors from folders not existing or empty cells in the list.

Be careful though, you won’t be able to undo this. Once it’s gone, it’s gone!

Renaming Folders from a List

You might already have the folders set up but need to rename them to some new naming convention. When I was setting up my folders, I have the forethought to prefix them with a number including leading zeros so that sorting them alphabetically arranged them in the order I wanted them to appear.

The named range to read the path is called RenameFolderPath and the table containing my list of folder names and their corresponding new name is called RenameFolderNames.

Sub RenameFolders()

Dim Folder As Range
Dim FolderPath As String
    
FolderPath = Range("RenameFolderPath").Value
    
On Error Resume Next
    
For Each Folder In Range("RenameFolderNames[Original Name]")
    Name FolderPath & "\" & Folder.Text As FolderPath & "\" & Folder.Offset(0, 1).Text
Next Folder

End Sub

We use a similar structure of VBA with the Name As command to rename our folders. We just need to reference our New Name and we can do that using an Offset from the Original Name since it is one column to the right of the Original Name.

Again, there’s no undo here, so be careful!