Copying rows with cells containing a certain value to a new sheet

Question

I am trying to copy rows with a certain value to another sheet to form a new table.

Wherever I have an A in a row (as shown in picture) copy that row and insert it in a new sheet. There are 2368 rows in the table.

Sub find_copy_row()
  Sheets.Add.Name = "Equilibrage.actif"

  Dim Rng As Range
  Dim Workrng As Range

  For i = 2 To i = 2368
    Set Workrng = Range(Rows("i"))

    For Each Rng In Workrng
      If Rng.Value = "A" Then
        Rows("i").Select
        Selection.Copy

        Sheets("Equilibrage.actif").Activate
        Rows("1:1").Insert    
      End If
    Next
   
    i = i + 1
  Next    
End Sub

Answer 1

Your code will not work for a variety of reasons (your For loop statement is incorrect, you Activate a sheet but never re-Activate the original, and more). So here is an example to get you started with some basic "rules" to live by when you're coding in VBA.

Option Explicit

Sub test()
    CopyRowsWith "A"
End Sub

Sub CopyRowsWith(checkValue As Variant)
    Dim destinationSheet As Worksheet
    Set destinationSheet = ThisWorkbook.Sheets.Add
    destinationSheet.Name = "Equilibrage.actif"
    
    Dim destRow As Long
    destRow = 1
    
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
    
    Dim lastRow As Long
    Dim lastColumn As Long
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
    lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    
    Dim i As Long
    Dim j As Long
    For i = 2 To lastRow
        For j = 1 To lastColumn
            If sourceSheet.Cells(i, j).Value = checkValue Then
                sourceSheet.Cells(i, j).EntireRow.Copy _
                        Destination:=destinationSheet.Range("A" & destRow)
                destRow = destRow + 1
                Exit For 'immediately skip to the next row
            End If
        Next j
    Next i
End Sub

1. Always use Option Explicit
2. Always be clear on what worksheet or range is being referenced
3. Use intermediate variables (such as lastRow) to help yourself make your code more readable. Yes, it's a few extra lines of code. But in many instances it can make your code faster (if that's a concern), but you'll find readability will always be a bigger help to you in the long run.
4. Avoid using Select in your code
5. Name your variables to be as clear as possible what you're trying to do.

Good luck!

Answer 2

Welcome Sakamoto, hopefully this will work for you (I commented out the worksheet add because it will fail if the worksheet already exists, you should check to see if it exists before adding it)

Sub RowCopy()

  ' Sheets.Add.Name = "Equilibrage.actif"
  Worksheets("Sheet3").Activate

  lastRow = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
  out_row = 2
  
  For in_row = 2 To lastRow
   q = Range("i" & in_row).Value
   If (q = "A") Then
    Range(in_row & ":" & in_row).Copy
    Worksheets("Equilibrage.actif").Range(out_row & ":" & out_row).Insert
    out_row = out_row + 1
   End If
  Next in_row
End Sub