VBA Pasting

[gvillepa]

Hi,

Ive created a helper cell in column S to identify some rows of data I want copied to a new sheet. The code below works, but the paste is literally pasting into a new sheet ("Alert") at the exact row number it was copied from. Some of these rows are thousands of rows apart from each other. I want it to copy the rows identified from the "DB" sheet, but paste it into "Alert" sheet at Row1, Row2, Row3, Row4, etc. Any help is appreciated. Thanks.

Sub Alert()
For Each Cell In Sheets("DB").Range("S:S")    
If Cell.Value = "1" Then        
matchRow = Cell.Row        
Rows(matchRow & ":" & matchRow).Select        
Selection.Copy

Sheets("Alert").Select       
ActiveSheet.Rows(matchRow).Select        
ActiveSheet.Paste        
Sheets("DB").Select    
End If
Next
End Sub

 

[Fluff]

If col S is created by formulae & contains either 1 or "" try

Sub CopyData()

    Sheets("DB").Columns(19).SpecialCells(xlFormulas, xlNumbers).EntireRow.Copy _
        Sheets("Alert").Range("A1")

End Sub

 

[MARK858]

@ gvillepa, the below should be a bit faster than the code you posted.
It would be better if you have header rows but you haven't stated that you do.

Sub Alert()
    Application.ScreenUpdating = False
    With Sheets("DB")
        .Rows("1:1").Insert Shift:=xlDown
        .Columns("S:S").AutoFilter 1, 1
        .UsedRange.SpecialCells(12).Copy Sheets("Alert").Cells(Rows.Count, "A").End(xlUp)(2)
        .UsedRange.AutoFilter
        .Rows("1:1").Delete Shift:=xlUp
        Sheets("Alert").Rows("1:1").Delete Shift:=xlUp
    End With
    Application.ScreenUpdating = True
End Sub

 

[gvillepa]

Fluff, TY. You have been helping me out greatly with the VBA - a newer realm for me. Works as intended!

MARK858. TY as well. I will use the code you supplied because it is indeed way faster and cleaner then what I was doing.

TY both!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[gvillepa]

I do have a header on the "DB" sheet that I would like copied over to the "Alert" sheet. How would you adjust based on that need? Thanks.

 

[MARK858]

Sub Alert2()
    Application.ScreenUpdating = False
    With Sheets("DB")
        .Columns("S:S").AutoFilter 1, 1
        .UsedRange.SpecialCells(12).Copy Sheets("Alert").Cells(Rows.Count, "A").End(xlUp)(2)
        .UsedRange.AutoFilter
    End With
    Sheets("Alert").Rows("1:1").Delete
    Application.ScreenUpdating = True
End Sub