title: Split data in one worksheet to multiple worksheets based on column in Excel using vba
body: <p>I am trying to split data in sheet 1 to multiple sheets based on the name column in cell A3 onwards. The problem that I am facing is I’m unable to track down the data if there are gap in between. Example the name starts from A3 to A100 and in between cell A10, A20 &amp; A30 is empty the program will only track down value from A3 to A9. The other problem for me is to specify the header. The header that I want to use start from cell A2, B2, C2 &amp; D2 and this program show the header as A1, B1, C1 &amp; D1 as there are value in that cell. This is my code.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  Dim ws As Worksheet, Rng As Range, cc
  Dim temp As Worksheet, CostC As Range, u
  
  Set ws = Sheets("Sheet1") 'where your original data. adjust to suit
  Set Rng = ws.Range("a1").CurrentRegion.Resize(, 15)
  Set CostC = ws.Range("a3", ws.Range("a" &amp; Rows.Count).End(xlUp))
  
  u = UNIQUE(CostC)
  Application.ScreenUpdating = 0
  For Each cc In u
      With Rng
          .AutoFilter field:=1, Criteria1:="=" &amp; cc
          On Error Resume Next
          Set temp = Sheets(cc)
          On Error GoTo 0
          If Not temp Is Nothing Then
  
  DoThis:
  
          .SpecialCells(xlCellTypeVisible).Copy temp.Range("A1")
          Else
              Set temp = Sheets.Add
              temp.Name = cc
              GoTo DoThis
          End If
          .AutoFilter
      End With
      Set temp = Nothing
  Next
  Application.ScreenUpdating = 1
  
  End Sub
  
  Function UNIQUE(r As Range)
  Dim a, v
  If IsArray(r.Value) Then
      a = r.Value
      With CreateObject("scripting.dictionary")
          .comparemode = vbTextCompare
          For Each v In a
              If Not IsEmpty(v) Then
                  If Not .exists(v) Then .Add v, Nothing
              End If
          Next
          If .Count &gt; 0 Then UNIQUE = .keys
      End With
      Erase a
  Else
      UNIQUE = r.Value
  End If
  
  End Function
  </code></pre>