Wolfgang5884
New Member
- Joined
- Nov 3, 2015
- Messages
- 5
Greetings MrExcel forum members. I'm a beginner when it comes to writing macros and have used this source before, but I'm having trouble with a new macro. Any help this amazing community can provide will be greatly appreciated.
My boss wants a monthly report listing all personnel that are going to have annual training due in that current month. For example, December's report had all people with a "Last Complete" date of any day in December of 2014. Bob would be in the report, but Joe and Dan would not. I threw together some VBA, but it does not work. Can you please tell me what I'm doing wrong?
Below is an example of the spreadsheet and the VBA code:
<tbody>
</tbody>
Sub This_Month()
Sheet1.Activate
Dim lr As Long, lr2 As Long, r As Long
lr = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
lr2 = Sheets("This Month").Cells(Rows.Count, "I").End(xlUp).Row
For r = lr To 2 Step -1
If Range("I" & r).Value = "=Month(Now()),Year(Now()-1)" Then
Rows(r).Copy Destination:=Sheets("This Month").Range("I" & lr2 + 1)
lr2 = Sheets("This Month").Cells(Rows.Count, "I").End(xlUp).Row
End If
Next r
End Sub
My boss wants a monthly report listing all personnel that are going to have annual training due in that current month. For example, December's report had all people with a "Last Complete" date of any day in December of 2014. Bob would be in the report, but Joe and Dan would not. I threw together some VBA, but it does not work. Can you please tell me what I'm doing wrong?
Below is an example of the spreadsheet and the VBA code:
A | B | C | D | E | F | G | H | I | J | K | |
1 | Office | Name | Job Position | Bulding | ID # | Start | Done | Last Complete | Last Archived | Percent Complete | |
2 | Bob | Yes | Yes | 12/29/2014 | Not Archived | ||||||
3 | Joe | Yes | Yes | 12/6/2015 | 12/1/2014 | ||||||
Dan | Yes | Yes | 11/4/2014 | 11/15/2013 |
<tbody>
</tbody>
Sub This_Month()
Sheet1.Activate
Dim lr As Long, lr2 As Long, r As Long
lr = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
lr2 = Sheets("This Month").Cells(Rows.Count, "I").End(xlUp).Row
For r = lr To 2 Step -1
If Range("I" & r).Value = "=Month(Now()),Year(Now()-1)" Then
Rows(r).Copy Destination:=Sheets("This Month").Range("I" & lr2 + 1)
lr2 = Sheets("This Month").Cells(Rows.Count, "I").End(xlUp).Row
End If
Next r
End Sub