Copy row to new sheet that matches criteria

[Kellis]

Hi all, I've tried a couple of different macros and I'm looking for best practice. I'm not too experienced with VBA but can normally follow code and adapt to my needs.

I am looking to copy all rows that contain 'Yes' in column 'P' sheet 'All' to sheet 'Incident'. So far everything I've used is very clunky and takes an age.

I would like to know best practice to allow this to be a smooth update.

The most basic code I've used is

Sub Test()
For Each Cell In Sheets("All").Range("P:P")
    If Cell.Value = "Yes" Then
        matchRow = Cell.Row
        Rows(matchRow & ":" & matchRow).Select
        Selection.Copy

        Sheets("Incidents").Select
        ActiveSheet.Rows(matchRow).Select
        ActiveSheet.Paste
        Sheets("All").Select
    End If
Next
End Sub

Any help is appreciated. Thanks

 

[Kellis]

I have now updated my code but if any of the experts have advice for me I'd love to hear it. This code still takes sometime to run but I o where near as long as the above code.

Public Sub CopyRows_clearData()

Application.ScreenUpdating = False

Sheets("All").Select

' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
NextRow_All = 2


    'clear previous contents (keep if you'd like to ensure no old data remains)
    lastrow = Sheets("Incidents").Range("A65536").End(xlUp).Row
    If lastrow > 1 Then
        With Sheets("Incidents").Range("A2:Z" & lastrow)
            .ClearContents
        End With
    End If
 
   
' Loop through each row
For x = 2 To FinalRow

    ' Decide if to copy based on column P
    ThisValue = Range("P" & x).Value
   
        If ThisValue = "Yes" Then
            Range("A" & x & ":Z" & x).Copy
            Sheets("Incidents").Select
            Range("A" & NextRow_All).Select
            ActiveSheet.Paste
            Sheets("All").Select
            NextRow_All = NextRow_All + 1
     
        End If
   
Next x

Application.ScreenUpdating = True

End Sub

 

[SirJB7]

Hi, Kellis!
Check if this is suitable for you, the first part (JLL file) does something very similar to your requirements.
http://chandoo.org/forum/threads/copy-data-from-various-sheets-onto-a-master.35180
Regards!

 

[Marc L]

Hi !

I'm not too experienced with VBA but can normally follow code and adapt to my needs.

I am looking to copy all rows that contain 'Yes' in column 'P' sheet 'All' to sheet 'Incident'. So far everything I've used is very clunky and takes an age.

Easier & quicker is to use Excel inner features !
Here just an advanced filter (tip : start from result sheet ) …

Even within a code an advanced filter is far easier & faster
than any classic gas factory code !

Must read :
- Introduction to Excel Advanced Filters
- Extract data using Advanced Filter and VBA
​

 

[Monty]

Really helpful marc!