title: Normalise Header on all worksheets VBA
body: <p>I'm new to VBA and have been playing around with the basics. </p>
  
  <p>What I'm tasked to do is to extract data from a certain row based on the header and column's data. Example, if column under the header "ENG JOBSCOPE" &lt;> "", then extract row of that data. </p>
  
  <p>However, i'm stuck at a point where when the macro loops thru all the worksheets, if the criteria that i want to find using range.find could not find, it'll give me error 91. </p>
  
  <p>I've read up about using normalise but i can't seem to make it work. </p>
  
  <p>Currently i'm using this code</p>
  
  <pre><code>J = 1
  
  For Each ws In x.Worksheets
  
       For Each wks In y.Worksheets
  
         With x.Worksheets(ws.Name)
           LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         End With
  
      For i=2 to Lastrow
  
       EJOB = Range("A1:DE1").Find(What:="ENG JOBSCOPE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False).Column
       'Error 91 comes from the above line ^^^
  
         EJobs = ws.Cells(i, EJOB).Value
  
       If EJobs &lt;&gt;  "" then
            x.Sheets("ID").Rows(i).Copy
            y.Sheets("ID").Range("A" &amp; j).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            J=J+1
  
       End if
      Next i
     Next wks
    Next ws
   End sub
  </code></pre>
  
  <p>"ENG JOBSCOPE" is a header. However, some of the worksheet consist of "ENG JOB SCOPE". </p>
  
  <p>Is there another method where i can use to make it so that it'll find the column number regardless of space or capitalization in between? </p>
  
  <p>Also, some of the worksheet doesn't consist of the "ENG JOBSCOPE". Is there a way for the code to continue searching without it stopping with error 91? 
  I've tried using on error goto next, but the data gets jumbled up. </p>
  
  <p>I hope what i typed is sufficient enough or clear enough. If it isn't clear enough please tell me what is needed to type as i'm new to this forum. </p>
  
  <p>Thank you very much in advance!</p>