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
CodePudding user response:
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
- Always use
Option Explicit
- Always be clear on what worksheet or range is being referenced
- 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. - Avoid using
Select
in your code - Name your variables to be as clear as possible what you're trying to do.
Good luck!
CodePudding user response:
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