0

Extended Question from- Excel: How to copy a row if it contains certain text to another worksheet (VBA)

I'm looking to modify the below code so that it's able to copy from sheet 2 to sheet 4, pasted adjacent (L:U)? see pictures.

pic2

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
         ' Copy>>Paste in 1-line (no need to use Select)
        .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
    End If
Next Cell
End With

End Sub

Comment Reply with picture pic3

Hufflegigans
  • 15
  • 1
  • 1
  • 6

1 Answers1

0

Like this?

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
         ' Copy>>Paste in 1-line (no need to use Select)
        .Range("A" & Cell.Row & ":J" & Cell.Row).Copy Destination:=Sheets(4).Range("L" & Sheets(4).Cells(Sheets(4).Rows.Count, "L").End(xlUp).Row + 1)
    End If
Next Cell
End With

End Sub

Macro to copy links:

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
        .Range("A" & Cell.Row & ":J" & Cell.Row).Copy
         ' Paste as Links requires to select a destination cell
        Sheets(4).Range("L" & Sheets(4).Cells(Sheets(4).Rows.Count, "L").End(xlUp).Row + 1).Select
        ActiveSheet.Paste Link:=True
    End If
Next Cell
End With

End Sub
Egan Wolf
  • 3,533
  • 1
  • 14
  • 29
  • does the trick however when i click the grab marco twice it just pastes same data underneath? – Hufflegigans Jul 21 '17 at 07:10
  • i added a picture (pic3) of result – Hufflegigans Jul 21 '17 at 07:16
  • @Hufflegigans Every time you run this macro it will copy all data. I added another solution. It will copy links to your source data, so there will be no need to update it (I can't test it ATM, hope it works). – Egan Wolf Jul 21 '17 at 07:43