Can anyone see whats wrong with this?

[JohnPoole]

Hi everyone, I've got a piece of code from a previous Mr Excel thread which looks at the date in column H, and if its older than 30 days it deletes the line and copies it to another sheet.

I've modified the code slightly so that it now looks at column A and just makes a copy of the line in another workbook instead if the date lis ess than the date shown in cell H1. ie if cell H1 contains the number 10, all lines with a date 10 days ago or less will be copied to the second workbook.

This works mostly, however say for example I have 10 lines, with various different dates in column A, lets say ranging from 10 to 100 days previous. If I look to copy lines less than 50 days old the procedure will work correctly, but if i try it with say 100 days it fails completely and tells me there are no lines to copy, even though i know there as it works with dates which are less than 50 days old.

I've tried it out with large samples of data, and using numbers 1-300 for the number of days back to search for and can find no reason why sometimes the procedure works and sometimes not.

A copy of the code i'm using is here

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> MBranch_Report_John()
Sheets("All").Select
daysback = [h1].Value
ChDir "W:\Cancellations\NEW ORDER BOOKS\Old order books\Newreport"
    Workbooks.Open Filename:= _
        "W:\Cancellations\NEW ORDER BOOKS\Old order books\Newreport\John.xls"
        
Sheets("Movement").Select

<SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, CriDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, rng <SPAN style="color:#00007F">As</SPAN> Range
CriDate = Format(Date - daysback, "mm/dd/yyyy")
<SPAN style="color:#007F00">'CriDate = Format(Date - 30, "mm/dd/yyyy") ' this was the code for getting the date in the original thread.</SPAN>

Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">With</SPAN> Sheets("Movement")

    LastRow = .Cells(Rows.Count, "a").End(xlUp).Row
    .[a:a].AutoFilter Field:=1, Criteria1:=">" & CriDate <SPAN style="color:#007F00">' ive chnage column H to A, and criteria 1 from < to ></SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = .Range("a27:a" & LastRow).SpecialCells(xlCellTypeVisible)
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">If</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        .[a:a].AutoFilter
        <SPAN style="color:#007F00">'.[b1].Select</SPAN>
        <SPAN style="color:#00007F">GoTo</SPAN> NoRows
    Else:
     Windows("CanxCodesReportX.xls").Activate
        rng.EntireRow.Copy Sheets("All").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
      
        <SPAN style="color:#007F00">'rng.Delete ' omitted this line so it no longer deltes the record after copying.</SPAN>
        .[a:a].AutoFilter
        <SPAN style="color:#007F00">'.[b1].Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">GoTo</SPAN> Xit
NoRows:
Err.Clear
MsgBox "No rows to report in John's Movement."

Xit:
<SPAN style="color:#00007F">Set</SPAN> rng = <SPAN style="color:#00007F">Nothing</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>

NDBranch_Report_John
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

I've noted on there the bits i've changed to make it clearer...hopefully.

Can anyone tell me what the problem is, or if there is an easier solution to the problem?

As a further question, this procedure is actually run twice, once for the sheet call Movement, and again for a sheet called New Deals, is there anyway to implement a With statement so that it can perform the task on both sheets with out the need to call the procedure for each sheet?

Any help with this appreciated as always......

 

[Norie]

John

I think you've placed the If in the wrong place.

Put it before you do anything.

By the way you should remove the error handling, it could be hiding any errors the code generates.

 

[JohnPoole]

Yay! that seems to do the trick. Many thanks Norrie for all your patience and help with this!