Filter and Move Row Macro
 

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.

Filter and Move Row Macro
 

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.

Filter and Move Row Macro
 

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.

Filter and Move Row Macro
 

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.

Filter and Move Row Macro
 

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.