title: Copy certain region with the help of search and append on main Excel spreadsheet
body: <p>I have a workbook with 200+ Excel spreadsheets with the same structure. On these sheets the value for <em>Theme</em> is always in cell C2 and the value for <em>Date</em> is always in C7, but when it comes to <em>Root_cause</em> and <em>Solutions</em> they start from different rows. </p>
  
  <p><a href="https://i.stack.imgur.com/uITCY.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uITCY.jpg" alt="https://imgur.com/aIxHJtH"></a></p>
  
  <p>I need to copy this information on the main sheet and append it: </p>
  
  <p><a href="https://i.stack.imgur.com/eKffD.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eKffD.jpg" alt="enter image description here"></a></p>
  
  <p>Maybe it's a good idea to use the find function to find the word 'Root_cause', then choose one column to the right and drag down to copy all related rows?</p>
  
  <p>Code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub Protocol()
  Dim wsheet As Worksheet
  
  With ThisWorkbook.Sheets("Main")
      For Each wsheet In ThisWorkbook.Sheets
          If wsheet.Name &lt;&gt; "Main" Then
  
                  Set Date = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
                  Set Theme = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
                  Set Root_cause = .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0)
                  Set Solutions = .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
  
                  Date.Value = wsheet.Range("C7").Value
                  Theme.Value = wsheet.Range("C2").Value
                  #Then I need to use FIND function on each sheet, come to word 'Root_cause', choose all rows for Root_cause and Solutions, copy them and append on sheet "Main"
  
                  End If
  
          Debug.Print wsheet.Name
      Next wsheet
  
  End With
  
  End Sub
  </code></pre>