VBA: Filter with 2 requirements, then move rows to a new sheet

[pramirez98]

Can anyone help me with a VBA code for:

Step 1. Filtering:
a. older than today
b. and anything that = 0
Step 2. Move Rows to a different sheet at the next blank row

I've tried using the macro recorder then manipulating the code, but have been unsuccessful. I am a novice at VBA. Please help.

Thanks!!!

 

[Peter_SSs]

Welcome to the MrExcel board!

I have a few questions - in red below.

Can anyone help me with a VBA code for:

Step 1. Filtering: What sheet name?
a. older than today What column?
b. and anything that = 0 What column?
Step 2. Move Rows to a different sheet What sheet name? at the next blank row

I've tried using the macro recorder then manipulating the code, but have been unsuccessful. I am a novice at VBA. Please help.

Thanks!!!

Also, in the original sheet ..

- Does that data start in A1?

- Is there a fixed number of columns? Which columns?

 

[pramirez98]

1. Sheet name = sheet1
1a. Column J
1b. Column N
2. Move to = sheet2

Thanks!

 

[Peter_SSs]

I have assumed that your data starts in column A (you didn't answer my last couple of questions )

I also wasn't sure if the data being copied to sheet2 was supposed to also be removed from sheet1. Post back if that needs to be done and you need assistance.

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Filter_Copy()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet, ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("sheet1")<br>    <SPAN style="color:#00007F">Set</SPAN> ws2 = Sheets("sheet2")<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    d = <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ws2.UsedRange<br>        nr = .Row + .Rows.Count<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ws1.UsedRange<br>        .AutoFilter Field:=10, Criteria1:="<" & d<br>        .AutoFilter Field:=14, Criteria1:="=0"<br>        .Offset(1).Copy Destination:=ws2.Range("A" & nr)<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pramirez98]

This worked great! Would it be difficult to remove the copied data from sheet1? Also when copying data to sheet2 it is being copied to the next empty row?

Thanks, this is really helping out a lot!

 

[Peter_SSs]

Would it be difficult to remove the copied data from sheet1?

Provided your dataset is not too large, try just adding the blue line of code.

.Offset(1).Copy Destination:=ws2.Range("A" & nr)
.Offset(1).EntireRow.Delete
.AutoFilter

Also when copying data to sheet2 it is being copied to the next empty row?

I'm not sure whether that really is a question or a statement. The data should be copied to the next vacant row.

 

[pramirez98]

Thanks! Works perfectly!