0

a noob here who just started using VBA and macros this week and i am trying to copy rows with a certain value to another sheet in order to form a new table. the idea is that wherever i have an A in a row (as shown in pic) i must copy that row and insert it in a new sheet. there is 2368 rows in the table so i tried the following code but it still wont work. thanks for helping guys !

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

table

JNevill
  • 42,519
  • 3
  • 32
  • 55
Sakamoto
  • 5
  • 2

2 Answers2

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!

PeterT
  • 7,741
  • 1
  • 15
  • 38
  • Thanks a lot for the advices, im still learning and honestly taking bigger tasks earlier than i should. – Sakamoto Feb 24 '22 at 16:53
0

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
Donald Koscheka
  • 146
  • 6