title: Excel VBA macro to copy specific rows from workbook sheets into new summary sheet....almost works
body: <p>I need to be able to look at a specified range of cells in every worksheet of my workbook and if they meet criteria, copy that row to a summary sheet.  The below code works for the most part except there are a few instances where it copies rows that do not meet the criteria and one instance where it skips a row it should have copied. </p>
  
  <p>Is there a way to use a debug tool so that at any time while cycling through the code I can see: What is the active sheet? What is the active cell? What is the active row? etc.  </p>
  
  <p>Also, should I use a -For Each Cell in Range- instead of -While Len- to loop through the specified range on each sheet?</p>
  
  <pre><code>Sub LoopThroughSheets()
  
  Dim LSearchRow As Integer
  Dim LCopyToRow As Integer
  Dim ws As Worksheet
  
  'Start copying data to row 2 in HH (row counter variable)
  LCopyToRow = 2
  
  For Each ws In ActiveWorkbook.Worksheets
  
  'Start search in row 7
  LSearchRow = 7
  
  While Len(ws.Range("M" &amp; CStr(LSearchRow)).Value) &gt; 0
  
  'If value in column M &gt; 0.8, copy entire row to HH
  If ws.Range("M" &amp; CStr(LSearchRow)).Value &gt; 0.8 Then
  
      'Select row in active Sheet to copy
      Rows(CStr(LSearchRow) &amp; ":" &amp; CStr(LSearchRow)).Select
      Selection.Copy
  
      'Paste row into HH in next row
      Sheets("HH").Select
      Rows(CStr(LCopyToRow) &amp; ":" &amp; CStr(LCopyToRow)).Select
      ActiveSheet.Paste
  
      'Move counter to next row
      LCopyToRow = LCopyToRow + 1
  
      'Go back to active ws to continue searching
      ws.Activate
  
  End If
  
  LSearchRow = LSearchRow + 1
  
  Wend
  
  
  Next ws
  'Position on cell A1 in sheet HH
  Sheets("HH").Select
  
  Application.CutCopyMode = False
  Range("A1").Select
  MsgBox "All matching data has been copied."
  
  End Sub
  </code></pre>