title: Find Copy and Paste in VBA macro
body: <p>I am trying to write a macro which search data from one sheet and copy's to another.</p>
  
  <p>But now I have a problem because I want to copy data between two searches and paste the whole data from multiple cells into one single cell.</p>
  
  <p><a href="https://i.stack.imgur.com/2rPxe.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2rPxe.jpg" alt="enter image description here"></a></p>
  
  <p>For example in the above picture my macro:</p>
  
  <ol>
  <li><strong>SEARCH</strong> for "--------------" and "*****END OF RECORD" </li>
  <li><strong>COPIES</strong> everything in between , here example data in row 29 and 30 and from column A,B,C</li>
  <li><p><strong>PASTE</strong> all the data from multiple cells A29,B29,C29 and then A30,B30,C30 to single cell in sheet 2 say cell E2.</p></li>
  <li><p>This pattern is reoccurring in the column A so I want to search for the next occurrence and do all the steps 1,2,3 and this time I will paste it in Sheet2 , cell E3.</p></li>
  </ol>
  
  <p>Below is the code:
  I am able to search my pattern but hard time in giving references to the cells in between those searched patterns and then copying all the data to ONE cell.</p>
  
  <pre><code> x = 2: y = 2: Z = 7000: m = 0: n = 0
  
  Do
  x = x + 1
  If ThisWorkbook.Sheets("lic").Range("A" &amp; x) = "---------------------" Then m = x
  If ThisWorkbook.Sheets("lic").Range("A" &amp; x) = "****** END OF RECORD" Then n = x
  If (n &gt; 0) Then
  
      Do
      For i = m To n
      ThisWorkbook.Sheets("lic").Range("A" &amp; i + 1).Copy
      ThisWorkbook.Sheets("lic").Range("B" &amp; i + 1).Copy
      ThisWorkbook.Sheets("lic").Range("C" &amp; i + 1).Copy
  
  
  'If (n &gt; 0) Then ThisWorkbook.Sheets("Sheet1").Range("E" &amp; y) = ThisWorkbook.Sheets("lic").Range("A" &amp; m + 1, "C" &amp; n - 1): y = y + 1
  'If (n &gt; 0) Then ThisWorkbook.Sheets("Sheet1").Range("E" &amp; y).Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value: y = y + 1
  Loop While Not x &gt; Z
  
  'Driver's Licence #:Driver's Licence #:Driver's Licence #:
  
  x = 2: y = 2: Z = 7000: counter = 1
  Do
  x = x + 1
  If ThisWorkbook.Sheets("lic").Range("A" &amp; x) = "Driver's Licence #:" Then counter = counter + 1
  If (counter = 2) Then ThisWorkbook.Sheets("Sheet1").Range("B" &amp; y) = ThisWorkbook.Sheets("lic").Range("C" &amp; x): y = y + 1: counter = 0
  If x = Z Then Exit Sub
  Loop
  End Sub
  </code></pre>