title: Excel VBA script to copy columns into separate text files under separate directories
body: <p>I have a worksheet that contains 27 columns, first row is the columns headers which are A-Z and NUM totaling 27 cols. Each column has a very long list of restricted urls sorted to the letter of the column, and the last (27th) column is for urls that start with a number. The columns' length is between 300-600 thousand cells.</p>
  
  <p>What I am after is to copy each column to a separate text file (*.file) under separate folders, ie column A is to be copied and saved to c:/blacklist/A/a.file, and so on, thus we get c:/blacklist/B/b.file all the way to c:/blacklist/NUM/num.file.</p>
  
  <p>I have been searching for a solution and found the following VBA script, which is very close to what I am after, at: <a href="http://www.ozgrid.com/forum/showthread.php?t=142181" rel="nofollow">http://www.ozgrid.com/forum/showthread.php?t=142181</a></p>
  
  <pre><code>Option Explicit 
  
  Public Sub Columns_2_TextFile() 
  
  Const My_Path = "C:\TEXTFILES\" 
  Dim iCol As Integer 
  Dim lRow As Long 
  Dim File_Num As Long 
  
  On Error Resume Next 
  If Trim(Dir(My_Path, vbDirectory)) = "" Then 
      MkDir My_Path 
  Else 
      Kill My_Path &amp; "*.txt" 
  End If 
  On Error Goto 0 
  File_Num = FreeFile 
  With ActiveSheet 
      For iCol = 2 To 256 
          Open My_Path &amp; Trim(.Cells(2, iCol).Value) &amp; ".txt" For Output As #File_Num 
          For lRow = 3 To .Cells(Rows.Count).End(xlUp).Row 
              Print #File_Num, .Cells(lRow, iCol).Value 
          Next 
          Close #File_Num 
      Next 
  End With 
  
  MsgBox "All files created and saved to : " &amp; My_Path 
  
  End Sub 
  </code></pre>
  
  <p>There are two issues with this script:
  the first is that it does not create text files under separate folders, instead it creates all files under ONE folder.
  the second is that when I tried it, it did not copy columns contents in the created files, in other words files were empty with zero contents.</p>