title: Why is my for next loop not working properly?
body: <p>I'm new to this site as well as to VBA. I've been working on a project and have run into a wall. I hope someone can help me out. What I'm trying to do is create a loop that will go through the specified sheet and pull data that matches my criteria, copy and paste it to another sheet, where I will be calculating it and then showing the results of the calculations in another sheet. </p>
  
  <p>so, I have the following code (excel VBA) that is suppose to go through the sheet and pull all records that match the current week (I'm also trying to add the current year, no luck so far) and paste all matching records to the sheet named Archieve:</p>
  
  <pre><code>Sub DataByWeek()
  
  Dim cw As Integer ' current week
  Dim cy As Integer ' current year
  Dim lr As Long 'last row of data
  Dim i As Long ' row counter
  
  'Get week number of today's date
  cw = Format(Date, "ww")
  cy = Format(Date, "yyyy")
  
  ActiveWorkbook.Worksheets("Daily DB").Activate
  
  ' Find last row of data plus one down
  lr = Sheet7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  
  For i = 2 To lr
      If Cells(i, 6) = cw Then
          Range(Cells(i, 1), Cells(i, 5)).Copy
          ActiveWorkbook.Worksheets("Archieve").Activate
          Range("A2").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
          End If
  Next i
  
  End Sub
  </code></pre>
  
  <p>This code does everything I want it to do except return beyond the first matching iteration. The code does go through all data on the sheet named Daily DB, but only returns the first matching record. I've tried looking online (many site including this one) to see if I missed something or did something wrong, but I can't find where I went wrong. </p>
  
  <p>I would also like to know how I can add a second criteria to the If statement condition. I'd like to add the year so that the condition reads something like </p>
  
  <pre><code>If Cells(i, 6 &amp; 7) = cw &amp; cy Then
  ...
  </code></pre>
  
  <p>Where i, 6 contains the week number and i, 7 contains the year. In other words, I'd like to 'say' find all records that contain the x week of x year.</p>
  
  <p>Sorry if this was too long and thank you in advanced for any and all help.</p>