title: Dynamic Folder Creation in VBA Excel
body: <p>I have several files that need to be distributed into respective folders. How can I check to see if a folder exists with a dynamic name using an excel VBA macro? </p>
  
  <p>I split a single workbook into several by the various worksheets within it. I believe it would be easiest if the macro used the name of each sheet in the original workbook to check for the existence of that folder. That way it's dynamic and I don't have to worry about coding it to search for each folder, as the data source continues to grow and need additional worksheets. I already have a code for searching for the folder, I just need to understand how to write it so that its dynamic.</p>
  
  <pre><code>Dim Path As String
  Dim Folder As String
  Dim Answer As VbMsgBoxResult
  Dim NewPath As String
  NewPath = ActvieWorkbook.Sheets.Name
  Path = "C:\Test" &amp; NewPath
  Folder = Dir(Path, vbDirectory)
  For Each sheetz0r In ActiveWorkbook.Sheets
  If Folder = vbNullString Then
      Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
      Select Case Answer
          Case vbYes
              VBA.FileSystem.MkDir (Path)
          Case Else
              Exit Sub
      End Select
  End If
  Next
  </code></pre>
  
  <p>In the code I have written, I just need the "NewPath =" line adjusted so that it will search for the sheet names.</p>