title: Excel VBA to copy and paste an entire row from 1 worksheet to another in the same workbook based on a text
body: <p>I'm really new to VBA and i will try my best in framing the question well and easy to understand.</p>
  
  <p>Here is my example in Excel with 9 lines/rows:</p>
  
  <p>Hello_update1_@time10<br>
  1 Today is a sunny day<br>
  2 Today is a good day<br>
  Hello_update2_@time20<br>
  3 Today is a rainy day<br>
  4 Today is a bad day<br>
  Hello_update2_@time30<br>
  5 Today is a pleasant day<br>
  6 Today is a good day  </p>
  
  <p>I have already used a code for finding a row with a specific text (for eg: "good") and copying it a new worksheet as shown below. But i need to add a code in which after i find the row with the text "good", the 1st line with "Hello" coming right above the row with "good" also to be copied and pasted in the new worksheet. Like here, the line "Hello_update1_@time10" has to be copied and pasted 1st and then "2 Today is a good day" should come and so on, ie, the final result should be:</p>
  
  <p>Hello_update1_@time10<br>
  2 Today is a good day<br>
  Hello_update2_@time30<br>
  6 Today is a good day</p>
  
  <pre><code>Sub find_good_copy()
      Dim K As Long, r As Range, v As Variant
      K = 2
  
      Dim w1 As Worksheet
      Dim w2 As Worksheet
      Set w1 = Tabelle1
      Set w2 = Tabelle3
  
      w1.Activate
  
      For Each r In Intersect(Range("B:B"), ActiveSheet.UsedRange)
          v = r.Value
          If InStr(v, "good") &gt; 0 Then
              w2.Cells(1, 1) = "good"
              r.EntireRow.Copy w2.Cells(K, 1)
              K = K + 1
          End If
      Next r
  End Sub
  </code></pre>
  
  <p>Tabelle1 and Tabelle3 are the names of the worksheets used.
  Currently, my output using the above code is:<br>
  2 Today is a good day<br>
  6 Today is a good day</p>
  
  <p>Thanks.</p>