Searching a text value in multiple excel files and copying below cell value to master file <p>I was working with a code to extract data from multiple excel files in a folder from muliple cells and paste the extracted values to a master file. For example Name was in cell A9, Phone in cell B6 etc. </p> <p>But now there was change in raw data recived and cell places are changed dynamically. The only thing same through which i can find those values is that through searching by text, if I have to find "Name" I need the code to first find text "Name" and the copy the value below the found cell. That is if "Name" found in cell "A10" then I need the code to copy value "A11", same way find text "Phone" and if text found in cell "B23" copy value of "B24" and so on.</p> <pre><code>Sub Consolidate() Dim wkbkorigin As Workbook Dim originsheet As Worksheet Dim destsheet As Worksheet Dim ResultRow As Long Dim Fname As String Dim RngDest As Range Set destsheet = ThisWorkbook.Worksheets("Extractdata") Set RngDest = destsheet.Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0).EntireRow Fname = Dir(ThisWorkbook.Path &amp; "/*.xlsx") 'loop through each file in folder (excluding this one) Do While Fname &lt;&gt; "" And Fname &lt;&gt; ThisWorkbook.Name If Fname &lt;&gt; ThisWorkbook.Name Then Set wkbkorigin = Workbooks.Open(ThisWorkbook.Path &amp; "/" &amp; Fname) Set originsheet = wkbkorigin.Worksheets("Table 1") With RngDest .Cells(1).Value = originsheet.Range("A3").Value .Cells(2).Value = originsheet.Range("C21").Value .Cells(3).Value = originsheet.Range("E21").Value .Cells(4).Value = originsheet.Range("A23").Value .Cells(5).Value = originsheet.Range("A31").Value End With wkbkorigin.Close SaveChanges:=False 'close current file Set RngDest = RngDest.Offset(1, 0) End If Fname = Dir() 'get next file Loop End Sub </code></pre> <p>Kindly help me in making the changes with the below code as i am getting it right to work.</p> <p><a href="https://i.stack.imgur.com/2Ex63.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2Ex63.jpg" alt="I need the values highlighted in yellow"></a></p> <p>All the values highlighted in yellow needs to be copied. Only thing common is the words or texts above the highlighted cells as range of cells changes as per workbooks.</p>