I'm not quite sure how to formulate this macro, any help would be greatly
appreciated. Here is the functionality I am seeking:

1.) Filter column E for entries that are from last month. (current month -
1) (all entries in column E are of format mm/dd/yy hh:mm)
2.) Select those entries and move them to workbook bc-lastmonth.xls
worksheet 'Blood Cultures'
3.) Paste them to the bottom of that worksheet.

This macro would be manually triggered.

This will work

"Luke Slotwinski" wrote:

I'm not quite sure how to formulate this macro, any help would be greatly
appreciated. Here is the functionality I am seeking:

1.) Filter column E for entries that are from last month. (current month -
1) (all entries in column E are of format mm/dd/yy hh:mm)
2.) Select those entries and move them to workbook bc-lastmonth.xls
worksheet 'Blood Cultures'
3.) Paste them to the bottom of that worksheet.

This macro would be manually triggered.

My code didn't get pasted. Here it is again

Sub copylastmonth()

MyMonth = Month(Now())
MyYear = Year(Now())

If MyMonth = 1 Then
MyYear = MyYear - 1
MyMonth = 12
Else
MyMonth = MyMonth - 1
End If

With Workbooks("lastmonth.xls"). _
Worksheets("Blood Cultures")

LastRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row
RowCount = LastRow + 1

LastRowE = Cells(Rows.Count, "E"). _
End(xlUp).Row

Set RowERange = Range(Cells(1, "E"), Cells(LastRowE, "E"))
For Each cell In RowERange

If IsDate(cell) Then
If (Month(cell) = MyMonth) And _
(Year(cell) = MyYear) Then

cell.EntireRow.Copy _
Destination:=.Rows(RowCount & ":" & _
RowCount)
RowCount = RowCount + 1
End If

End If
Next cell
End With

End Sub


"Joel" wrote:

This will work

"Luke Slotwinski" wrote:

I'm not quite sure how to formulate this macro, any help would be greatly
appreciated. Here is the functionality I am seeking:

1.) Filter column E for entries that are from last month. (current month -
1) (all entries in column E are of format mm/dd/yy hh:mm)
2.) Select those entries and move them to workbook bc-lastmonth.xls
worksheet 'Blood Cultures'
3.) Paste them to the bottom of that worksheet.

This macro would be manually triggered.

Thank you Joel, that worked perfectly.
Luke Slotwinski

"Joel" wrote:

My code didn't get pasted. Here it is again

Sub copylastmonth()

MyMonth = Month(Now())
MyYear = Year(Now())

If MyMonth = 1 Then
MyYear = MyYear - 1
MyMonth = 12
Else
MyMonth = MyMonth - 1
End If

With Workbooks("lastmonth.xls"). _
Worksheets("Blood Cultures")

LastRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row
RowCount = LastRow + 1

LastRowE = Cells(Rows.Count, "E"). _
End(xlUp).Row

Set RowERange = Range(Cells(1, "E"), Cells(LastRowE, "E"))
For Each cell In RowERange

If IsDate(cell) Then
If (Month(cell) = MyMonth) And _
(Year(cell) = MyYear) Then

cell.EntireRow.Copy _
Destination:=.Rows(RowCount & ":" & _
RowCount)
RowCount = RowCount + 1
End If

End If
Next cell
End With

End Sub


"Joel" wrote:

This will work

"Luke Slotwinski" wrote:

I'm not quite sure how to formulate this macro, any help would be greatly
appreciated. Here is the functionality I am seeking:

1.) Filter column E for entries that are from last month. (current month -
1) (all entries in column E are of format mm/dd/yy hh:mm)
2.) Select those entries and move them to workbook bc-lastmonth.xls
worksheet 'Blood Cultures'
3.) Paste them to the bottom of that worksheet.

This macro would be manually triggered.

Two Quick questions:
Could I replace the command cell.EntireRow.Copy _ with cell.EntireRow.Cut _
to cut the line and paste it in the lastmonth.xls?

"Joel" wrote:

My code didn't get pasted. Here it is again

Sub copylastmonth()

MyMonth = Month(Now())
MyYear = Year(Now())

If MyMonth = 1 Then
MyYear = MyYear - 1
MyMonth = 12
Else
MyMonth = MyMonth - 1
End If

With Workbooks("lastmonth.xls"). _
Worksheets("Blood Cultures")

LastRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row
RowCount = LastRow + 1

LastRowE = Cells(Rows.Count, "E"). _
End(xlUp).Row

Set RowERange = Range(Cells(1, "E"), Cells(LastRowE, "E"))
For Each cell In RowERange

If IsDate(cell) Then
If (Month(cell) = MyMonth) And _
(Year(cell) = MyYear) Then

cell.EntireRow.Copy _
Destination:=.Rows(RowCount & ":" & _
RowCount)
RowCount = RowCount + 1
End If

End If
Next cell
End With

End Sub


"Joel" wrote:

This will work

"Luke Slotwinski" wrote:

I'm not quite sure how to formulate this macro, any help would be greatly
appreciated. Here is the functionality I am seeking:

1.) Filter column E for entries that are from last month. (current month -
1) (all entries in column E are of format mm/dd/yy hh:mm)
2.) Select those entries and move them to workbook bc-lastmonth.xls
worksheet 'Blood Cultures'
3.) Paste them to the bottom of that worksheet.

This macro would be manually triggered.