Excel Macro Not Working

[ydfoley]

I have never written a loop in Excel and hope I can get some assistance.
I have a spreadsheet named “AccessDBLink” that imports a table from Access. The last three columns of the spreadsheet are calculation fields. I need a macro that will take note of column “N”. If the cell says “Pass”, columns E, K, C, H, I & J will be copied to worksheet “CurrentReportPeiod” in columns A,B,C,D,E & F.
This is what my current macro says.
Sub GetPassDateRange()
Dim LR As Long, i As Long
With ActiveSheet
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To CurrentReportPeriod
If .Range("N" & i).Value = "Pass" Then
.Range("E" & i).Copy
Sheets("CurrentReportPeriod").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
.Range("K" & i).Copy
Sheets("CurrentReportPeriod").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next i
End With
End Sub
Nothing happens. I get no information copied over to the spreadsheet. What am I failing to do?

 

[ydfoley]

I was trying to get the first two columns to work before working on the others

 

[Rick Rothstein]

If you are up for an alternate method, here is a non-looping macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub GetPassDateRange()
  Dim LR As Long, LCplusOne As Long
  LR = Cells(Rows.Count, "N").End(xlUp).Row
  LCplusOne = Cells(1, Columns.Count).End(xlToLeft).Column + 1
  Application.ScreenUpdating = False
  Cells(1, LCplusOne).Resize(LR) = Evaluate("IF(N1:N" & LR & "=""pass"",""X"","""")")
  Cells(1, LCplusOne).Value = "X"
  Intersect(Columns(LCplusOne).SpecialCells(xlConstants).EntireRow, Range("E1,K1,C1,H1:J1").EntireColumn).Copy Sheets("CurrentReportPeriod").Range("A1")
  Columns(LCplusOne).Clear
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[My Aswer Is This]

edit

 

[ydfoley]

That worked perfectly. Thanks.