title: How Can I sort and split my validated Excel Data into Sheets with VBA?
body: <p>I have a large amount of data in 5 columns. I need to do the following based on column E (file path):</p>
  
  <ul>
  <li>verify the path is valid, if not try a secondary path and return the correct one, or an error value if neither are valid</li>
  <li>if there is a valid path, get the file size (size should be >0 )</li>
  <li>if there is a valid path, get the file extension (there are lots of "bad" extensions like nothing at all, a period with nothing after it, pdf[1], pdf, etc...) I can create a whitelist for which are "good".</li>
  </ul>
  
  <p>Once this is complete, I need to make one new sheet per file extension, and copy over the first 4 columns plus the valid path into the 5th column. There should be a "bad data" sheet as well for everything that didn't get "validated" as per above. On the original sheet, if it would be possible to create another column mapping to the new location of that row it would be great, but not required.
  Hopefully this example will make it a little more clear. I am using Excel 2010, the google spreadsheet is just for a visual example.</p>
  
  <p>Here is my sample data: <a href="https://docs.google.com/spreadsheets/d/127H7WRwHljYWX-46AAjzBrOvAlXkh4OcAF6jflaT-lI/edit#gid=0" rel="nofollow">https://docs.google.com/spreadsheets/d/127H7WRwHljYWX-46AAjzBrOvAlXkh4OcAF6jflaT-lI/edit#gid=0</a></p>
  
  <p>I have functions for the first three items, but the last with moving the data around I'm not sure how to accomplish.</p>
  
  <p>Here is a link to my 3 functions: <a href="http://pastebin.com/afRMgvTE" rel="nofollow">http://pastebin.com/afRMgvTE</a></p>
  
  <pre><code>Private Function FileExists(ByVal sPathName As String, Optional ByVal sPathName2 As String) As String
      On Error Resume Next
      If sPathName &lt;&gt; "" Then
          If Dir$(sPathName) &lt;&gt; "" Then
              FileExists = sPathName
          ElseIf sPathName2 &lt;&gt; "" And Dir$(sPathName2) &lt;&gt; "" Then
              FileExists = sPathName2
          Else
              FileExists = "NO VALID URL"
          End If
      End If
  End Function
  
  Private Function GetFileSize(ByVal sPathName As String) As Long
      On Error Resume Next
      GetFileSize = FileLen(sPathName)
  End Function
  
  Private Function GetFileExt(ByVal sPathName As String) As String
      On Error Resume Next
      Dim fso As New Scripting.FileSystemObject
      Dim currentExtension As String
  
      currentExtension = fso.GetExtensionName(Trim(sPathName))
  
      Dim validExtension As Object
      Set validExtension = CreateObject("Scripting.Dictionary")
  
      validExtension.Add "bmp", 1
      validExtension.Add "csv", 2
      validExtension.Add "doc", 3
      validExtension.Add "docm", 4
      validExtension.Add "docx", 5
      validExtension.Add "gif", 6
      validExtension.Add "htm", 7
      validExtension.Add "jpg", 8
      validExtension.Add "msg", 9
      validExtension.Add "pdf", 10
      validExtension.Add "ppt", 11
      validExtension.Add "rtf", 12
      validExtension.Add "tif", 13
      validExtension.Add "txt", 14
      validExtension.Add "vsd", 15
      validExtension.Add "xls", 16
      validExtension.Add "xlsm", 17
      validExtension.Add "xlsx", 18
      validExtension.Add "zip", 19
  
      If validExtension.Exists(LCase(currentExtension)) Then
          GetFileExt = currentExtension
      Else
          GetFileExt = "UNSUPPORTED"
      End If
  
  End Function
  </code></pre>