title: Referencing cells from other sheets
body: <p>I am trying to extract data from different sheets in a summary sheet.</p>
  <p>The referencing does not work.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Summary_LPI()
  
    Dim wkSht As Worksheet, wsC As Worksheet, rngSearch As Range
    Dim shNCell As Range
  
    Set wsC = Sheets(&quot;Summary&quot;)
    Set rngSearch = wsC.Range(&quot;A2:A60&quot;)
    For Each wkSht In ThisWorkbook.Worksheets
      'find the sheet name cell in rngSearch:
      Set shNCell = rngSearch.Find(what:=wkSht.Name, LookIn:=xlValues, Lookat:=xlWhole, 
     MatchCase:=False)
      'if found:
     If Not shNCell Is Nothing Then
        'copy the below built array in the necessary place
        wkSht.Range(&quot;AZ56&quot;).Value = wsC.Range(shNCell.Offset(0, 6), shNCell.Offset(1, 6)).Value
       
      End If
  Next wkSht
  
  End Sub
  </code></pre>
  <p><a href="https://i.stack.imgur.com/IvFXX.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IvFXX.jpg" alt="Referencing" /></a></p>