Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Quickly list all sheet names with hyperlinks in Excel

Kutools for Excel's Create List of Sheet Names utility can quickly create a list of clickable sheet names as below screenshot shown.  Click for full-featured 30 days free trial!
doc list sheet name
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Create folders based on cell values with VBA code

Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 30-day trial of Office Tab!

ot excel

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


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.
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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Praveen Dixit · 3 months ago
    It works good with english characters; but how about other characters like cyrillic? Thank you in advance.
  • To post as a guest, your comment is unpublished.
    A. Qusyairi · 5 months ago
    GREAT..!!

    Really really help me bro..

    God Bless You All..
  • To post as a guest, your comment is unpublished.
    Martz · 10 months ago
    It works great,Thank you
  • To post as a guest, your comment is unpublished.
    Dakota · 10 months ago
    Website looked a bit sketchy but this worked and saved me a lot of frustration. Thank you!
  • To post as a guest, your comment is unpublished.
    martin · 10 months ago
    it worked exactly as told.
    thanks!
  • To post as a guest, your comment is unpublished.
    stefan kosa · 1 years ago
    I am trying to use this file in a sharepoint folder to create folders. But I get the path error. Is it possible to change the code to work in a sharepoint folder the file is located ?
  • To post as a guest, your comment is unpublished.
    Mike · 1 years ago
    Is it possible to create a folder>subfolder import structure? Allowing me to have all the top level folders and their sub folders import all at once? They will be all the same.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry I do not get your point. Do you mean to create folder and subfolder based on cell values or to import folder and subfolder name in Excel? If the last one, yuo can apply Kutools for Excel's Create Filename List.
  • To post as a guest, your comment is unpublished.
    Jacob · 1 years ago
    Hi All, I'm a super newb, like some of you I don't know how to change the destination of where these files are created. The files are currently being created on my Desktop, is there a way I could change the destination to a SharePoint?
  • To post as a guest, your comment is unpublished.
    Genious fox · 2 years ago
    If you dont have enterprise licence of ms excel then its bit difficult, try the trick in the video and share like or subscribe



    https://youtu.be/1WAQWm7UPBY
  • To post as a guest, your comment is unpublished.
    Nelson Barbisa · 2 years ago
    Thank you very very much! you just made me save like 3 hours of work!
  • To post as a guest, your comment is unpublished.
    Rahul Upadhyay · 2 years ago
    How to make sub-folders also in this VBA?
  • To post as a guest, your comment is unpublished.
    Jw · 2 years ago
    Not working using Excel for Mac (v15.31)... run-time error '53': File not found.
    Is the "\" format not applicable on Mac?

    Bounces error on this line of code:
    If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0
  • To post as a guest, your comment is unpublished.
    Ted · 2 years ago
    Just saved me a few hours of work. I would fax you a beer if I knew your number!!!
  • To post as a guest, your comment is unpublished.
    Randy Marsh · 3 years ago
    Dude, you rock!! Thanks a million
  • To post as a guest, your comment is unpublished.
    Randy Marsh · 3 years ago
    Dude, you are awesome! Thank you ever so much!
  • To post as a guest, your comment is unpublished.
    Randy · 3 years ago
    Worked like a charm, but is there a way to create subfolders?
  • To post as a guest, your comment is unpublished.
    bhuppi · 3 years ago
    hi ,
    i m a newbie
    excel shoe error 52 while debugging this plz help
  • To post as a guest, your comment is unpublished.
    worker · 3 years ago
    Thanks for this. works fine
  • To post as a guest, your comment is unpublished.
    MKontul · 3 years ago
    Cheers! Great function!
  • To post as a guest, your comment is unpublished.
    Manikkavasagan R · 3 years ago
    Thanks a ton Dude,,, its working...
  • To post as a guest, your comment is unpublished.
    Manuel · 3 years ago
    you just saved me a lot of time pal
  • To post as a guest, your comment is unpublished.
    Vinay · 3 years ago
    Thanks a lot for this KUTOOLS & Enterprise tabs ......time saving tool..
  • To post as a guest, your comment is unpublished.
    srikar · 4 years ago
    You are awesome. It worked. Thanks.
  • To post as a guest, your comment is unpublished.
    Denny · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Vincent · 4 years ago
    Thanks you so much for this helpful post, saved my some time. I love nerds:) What would we do without them, seriously!
  • To post as a guest, your comment is unpublished.
    juliekadas · 5 years ago
    Worked like a charm, but is there a way to create subfolders?
    • To post as a guest, your comment is unpublished.
      Jaakko · 4 years ago
      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.
    • To post as a guest, your comment is unpublished.
      ktrazz · 5 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Pete Lewis · 5 years ago
    Thanks for this - Excellent time saver
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 5 years ago
    For some reason the active workbook is having problems with the path. HELPPPP :cry:
    • To post as a guest, your comment is unpublished.
      VC · 3 years ago
      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
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 5 years ago
    You are a lifesaver!!!!! [b]Thank you![b]

    Note: Don't put a space between MakeFolder and ().
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 5 years ago
    [b]HELP!!![/b] 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.
    • To post as a guest, your comment is unpublished.
      BJA101 · 5 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Yogesh · 5 years ago
    How to do this in LibreOffice or OpenOffice

    Please reply.
  • To post as a guest, your comment is unpublished.
    Charan · 5 years ago
    :-) Thanks for providing this data.... saved my lot of time
  • To post as a guest, your comment is unpublished.
    mike69 · 5 years ago
    Thanks a lot!!! This is great and most effective and easiest solution I've found on the internet for this! :lol:
  • To post as a guest, your comment is unpublished.
    SASAR · 5 years ago
    Thanks this save me a lot of time. But how to create sub folders?
  • To post as a guest, your comment is unpublished.
    karthiha devi · 5 years ago
    Good... :) Thank you.. It makes me to work simple.
  • To post as a guest, your comment is unpublished.
    Ahmed ElGammal · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Jozef · 6 years ago
    What is the fault in following VBA instruction?

    Do While r
    • To post as a guest, your comment is unpublished.
      skyyang · 6 years ago
      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!
      • To post as a guest, your comment is unpublished.
        HappyTang · 3 years ago
        SkyYang, [quote name="skyyang"]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![/quote]
        YOU ARE AWESOME !! I was able to be more productive at work because of the program (VBA code in Developer).