title: when i try adding an object to the collection, all the object values are changed to the current object, how?
body: <p>I have a excel workbook, I want to read the column values from 'input1' sheet, and based on the column value copy the row from 'input' sheet, and store it in a class object. Do it for all the column entries in 'input1' sheet.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
      Call PrepareOutput
  End Sub
  Public Sub PrepareOutput()
     Dim i, indexValue,inputIndexRow As Integer
     Dim bills As New Collection
     inputIndexRow = 2
     indexValue= Worksheets("Input1").Cells(inputIndexRow , 1).Value
  
     While (Not IsEmpty(indexValue))
        i = indexValue+ 1
        Dim bill As New bill
        bill.quantity = Worksheets("Input1").Cells(inputIndexRow , 2).Value
        bill.cost = Worksheets("Input").Cells(i, 3).Value
        bills.Add bill
        inputIndexRow = inputIndexRow + 1
        indexValue= Worksheets("Input1").Cells(inputIndexRow , 1).Value
     Wend
  End Sub
  
  'class Bill has these public variables
  Public service As String
  Public serialNumber As Byte
  Public cost As Double
  Public quantity As Byte
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/X1X6m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X1X6m.png" alt="After adding first object"></a></p>
  
  <p><a href="https://i.stack.imgur.com/Zld6p.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Zld6p.png" alt="after adding second object"></a></p>