title: How to assign variable to value from loop VBA
body: <p>my newbie question:</p>
  
  <p>I would need to define variable from values gathered by loop.
  I have column of datas, and I need to filter those data and copy to another new sheet named with variable.</p>
  
  <p>Problem is, I cannot get variable from loop. 
  Is it possible?
  Example: variable is "hu"</p>
  
  <pre><code>i = 2
  Do Until IsEmpty(Cells(i, 9))
  
   **hu** = Cells(i, 9).Value  
  
      i = i + 1
  Loop
  
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Name = **hu**
  
  Worksheets("Sheet1").Range("A1:I1").Select
      Selection.AutoFilter
      ActiveSheet.Range("$A$1:$I$1").AutoFilter Field:=9, Criteria1:=**hu**
  
  
  With ActiveSheet.AutoFilter.Range
   On Error Resume Next
     Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
         .SpecialCells(xlCellTypeVisible)
   On Error GoTo 0
  End With
  If rng2 Is Nothing Then
     MsgBox "No data to copy"
  Else
  
     Set rng = ActiveSheet.AutoFilter.Range
     rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
       Destination:=Worksheets("Comparison2").Range("A2")
  End If
     ActiveSheet.ShowAllData
  </code></pre>
  
  <p>Thanks!</p>