Note: The other languages of the website are Google-translated. Back to English

How to quickly create folders based on cell values in Excel?

Supposing you have a list of staff names in a range of a worksheet, and now you want to create some folders for each of them in the cells to record their information. If you create the folders one by one, it will waste a lot of time. But how could you quickly create these folders? Today, I will introduce you some quick tricks:

Create folders based on cell values with VBA code

Quickly create folders based on cell values with Kutools for Excelgood idea3


Create folders based on cell values with VBA code

For example, I have a range of names in a worksheet, I want to create folders for each of them, and save them to a specified path, with the VBA code, I can finish this task.

1. Put the active workbook into a specific directory which you will place the created folders into. Open the workbook and select the range of cells that you want to use.

doc-create-folders1

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

VBA code: create folders based on cell values

Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub

3. Click doc-multiply-calculation-3 button to execute the code. All the selected cells have been created to folders with their values. And the folders are placed into the path as same as the active workbook. See screenshot:

doc-create-folders2


Quickly create folders based on cell values with Kutools for Excel

It is easy and convenient for you to create folders based on cell vales if you use the tool- Kutools for Excel.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Select the range that you want to create folders.

2. Click Kutools Plus > Import / Export > Create Folders from Cell Contents…, see screenshot:

doc create folder 1

3. In the Create Folders from Cell Contents dialog box, click doc-create-folders3button to specify the path you want to save the folders. Seen screenshot:

doc create folder 2

4. Click OK. And a prompt box will remind you how many folders have been created. See screenshot:

doc create folder 3

5. Click OK. And all of the values in the selected range have been created folders in the specified folder.
doc create folder 4


List all file names from a folder into a sheet

to know more about this function.

Here is an utility in Kutools for Excel – Filename List can list all file names of a folder in a sheet, if you are interested in it, please go on reading.

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Click Kutools Plus > Import/Export > Filename List.
doc create folder 5

2. In the Filename List dialog, select a folder you want to list its files, the check All files or Specify option as you need in Files type section.
img src="https://cdn.extendoffice.com/images/stories/doc-excel/create-folders/doc-create-folder-6.png" alt="doc create folder 6" />

3. Click Ok, a new sheet is created with all file names and some relative information.
doc create folder 7


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (59)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What is the fault in following VBA instruction? Do While r
This comment was minimized by the moderator on the site
Thanks for your reply, the VBA code have little questions when I edit it, so here I have modified it, and it applys successfully, please try it. Good luck!
This comment was minimized by the moderator on the site
SkyYang, [quote]Thanks for your reply, the VBA code have little questions when I edit it, so here I have modified it, and it applys successfully, please try it. Good luck!By skyyang[/quote] YOU ARE AWESOME !! I was able to be more productive at work because of the program (VBA code in Developer).
This comment was minimized by the moderator on the site
Who ever wrote the first example - You are awesome!!
This comment was minimized by the moderator on the site
please, i need that same macro but instead of saving them as folders, i need it to save notepads with only the cell names in a column range, appreciate the support.
This comment was minimized by the moderator on the site
Good... :) Thank you.. It makes me to work simple.
This comment was minimized by the moderator on the site
Thanks this save me a lot of time. But how to create sub folders?
This comment was minimized by the moderator on the site
Thanks a lot!!! This is great and most effective and easiest solution I've found on the internet for this! :lol:
This comment was minimized by the moderator on the site
:-) Thanks for providing this data.... saved my lot of time
This comment was minimized by the moderator on the site
How to do this in LibreOffice or OpenOffice Please reply.
This comment was minimized by the moderator on the site
HELP!!! I Have over 7500 folders to make but the program is having a compiling error highlighting [b]Sub MakeFolders ()[b] as if it is an invalid name or something! I'm operating Excel 2010.
This comment was minimized by the moderator on the site
I had the same problem, but when I looked at the folder name closer there was a / and once I removed it, the code worked like a charm.
This comment was minimized by the moderator on the site
You are a lifesaver!!!!! [b]Thank you![b] Note: Don't put a space between MakeFolder and ().
This comment was minimized by the moderator on the site
For some reason the active workbook is having problems with the path. HELPPPP :cry:
This comment was minimized by the moderator on the site
I had a similar issue , at the end I figured it out that program does not work if the file name have slashes / or \ , I replaces / with _, it worked
This comment was minimized by the moderator on the site
Thanks for this - Excellent time saver
This comment was minimized by the moderator on the site
Worked like a charm, but is there a way to create subfolders?
This comment was minimized by the moderator on the site
Trying this on a Mac and I get "Run-time error '68': Device unavailable", and pressing debug points out the following line of code as the culprit: If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then MkDir (ActiveWorkbook.Path & "\" & Rng(r, c)) Please help.
This comment was minimized by the moderator on the site
I'd like to know this too. I nee to make sub folders with given name already added. Example: Excelnamedfolder\P\E I need two folders inside P folder named E and K.
This comment was minimized by the moderator on the site
Thanks you so much for this helpful post, saved my some time. I love nerds:) What would we do without them, seriously!
This comment was minimized by the moderator on the site
I'm trying to add multiple columns together when creating a folder. Any suggestions on how to do this? e.g. A B C 1 A1 A2 A3 2 B1 B2 B3 3 C1 C2 C3 End product once folders are created should be: Folder 1 (A1 + A2 + A3) Folder 2 (B1 + B2 + B3) Etc... I found multiple formulas on creating folders from single cells but nothing for multiple. I've also tried modifying a few things with no success. Any ideas? Thanks
This comment was minimized by the moderator on the site
You are awesome. It worked. Thanks.
This comment was minimized by the moderator on the site
Nice Job.....Excellent
This comment was minimized by the moderator on the site
Thanks a lot for this KUTOOLS & Enterprise tabs ......time saving tool..
This comment was minimized by the moderator on the site
you just saved me a lot of time pal
This comment was minimized by the moderator on the site
Thanks a ton Dude,,, its working...
This comment was minimized by the moderator on the site
Cheers! Great function!
This comment was minimized by the moderator on the site
Thanks for this. works fine
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations