1

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.

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:

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

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.

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 

If Cells(i, 6 & 7) = cw & cy Then
...

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.

Sorry if this was too long and thank you in advanced for any and all help.

user3646536
  • 11
  • 4
  • 7
    It executed once because you used `Activate` method. So on the second iteration, the routine is now referenced to `Archieve` and not on `Daily DB`. [Try reading this which might give you a hint on how to solve your issue](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select). – L42 May 17 '14 at 02:20
  • 2
    You have some good code there, I think you will be able to solve that first part on your own using the link @L42 provided :) For your second query, you can do `If Cells(i, 6) = cw And Cells(i, 7) = cy Then ...` – David Zemens May 17 '14 at 03:00
  • have a read of the answer to http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select?lq=1 that may give you some ideas (in particular, think of using worksheet variables) – Cor_Blimey May 17 '14 at 09:26
  • Thank you, L42, David Zemens and Cor_Blimey, for all your help. I got everything working just the way I want it to. – user3646536 May 18 '14 at 23:28

1 Answers1

0

If you add ActiveWorkbook.Worksheets("Daily DB").Activate before the End If statement, I believe it would fix your problem. I'm not sure it's the most efficient way though.

user3785199
  • 11
  • 3