title: How do i copy and paste data to worksheets that i created in VBA using for loop?
body: <p>Im trying to copy and paste my data and assign them into different worksheets.For example, if column F is martin 1, the entire row that has martin1 will be paste to worksheets("Index1"). Same thing for Charlie 1 and it will be paste to worksheets("Index2"). However, I faced with a object defined error here as shown in my code below. Any ideas how to solve it?</p>
  
  <pre><code>Sub SaveRangewithConsecutiveDuplicateValuestoNewSheet()
  'Define all variables
  Dim wb As Workbook, ws As Worksheet, sCel As Range, rwNbr As Long
  
  Set wb = ThisWorkbook 'Set workbook variable
  Set ws = wb.Worksheets("Sheet1") 'set worksheet variable using workbook variable
  
  Set sCel = ws.Cells(1, 6) 'Set the first start cell variable to test for duplicate values
              Dim i As Integer
              Dim site_i As Worksheet
              For i = 1 To 3
                 Set site_i = Sheets.Add(after:=Sheets(Worksheets.count))
                 site_i.Name = "Index" &amp; CStr(i)
                  Next i
  
      Application.DisplayAlerts = False
          For rwNbr = 2 To ws.Cells(ws.Rows.count, 6).End(xlUp).Offset(1).Row Step 1 'Loop
  
              If ws.Cells(rwNbr, 6).Value = "Martin1" Then
   ws.Range(sCel, ws.Cells(rwNbr, 6)).EntireRow.Copy Destination:=Sheets("Index1").Range("A1")
   ElseIf ws.Cells(rwNbr, 6).Value = "Charlie1" Then
      ws.Range(sCel, ws.Cells(rwNbr - ws.UsedRange.Rows.count, 6)).EntireRow.CopyDestination:=Sheets("Index2").Range("A1") '&lt;----application defined or object defined error here
  
              End If
  
          Next rwNbr
      Application.DisplayAlerts = True
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/ozVlY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ozVlY.png" alt="enter image description here"></a></p>
  
  <p>This is the link to my worksheet. <a href="https://www.dropbox.com/home?preview=Sample+-+Copy.xlsm" rel="nofollow noreferrer">https://www.dropbox.com/home?preview=Sample+-+Copy.xlsm</a></p>
  
  <p>The final output should look something like this...
  <a href="https://i.stack.imgur.com/o8zxY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o8zxY.png" alt="enter image description here"></a>
  <a href="https://i.stack.imgur.com/giYiT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/giYiT.png" alt="enter image description here"></a></p>