Changing a macro to not delete some lines

[sncb]

Hi All..Im new to this forum and needing some assistance.

I have this section of a macro that has conditional changes such that it deletes a few lines if the 6th column is a no..Instead I want it to copy those lines and paste them into another worksheet...Below is the original code..


Application.CutCopyMode = False
For intCounter = 2 To intNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
Rows(intCounter).Select
Selection.Delete Shift:=xlUp
intCounter = intCounter - 1
End If

 

[sncb]

So it worked and was adding the new records after yesterdays last record.

Also for adding no of days since the last run, I added a Days360 formula in col next to date column which is counting days since the last run.

Thanks once again for all your help.

 

[sncb]

Hi Vog,

In tab 'Test' after the data gets pasted in column 'R' the date gets populated in mmddyy format but I need it in ddmmyy format.

What would be the line code to change that?

Thanks

 

[VoG]

You just need to format the whole of column R to your desired date format.

 

[sncb]

Vog,

I run this macro about 5 times a day. When I run it the first time it adds new records after yesterday's last record but when I run it the 2nd time today it dosen't add any changes made in the source data to the last of today's record.

Anything that you can suggest?

Thanks

 

[sncb]

Vog,

Pls ignore that last message. Apparently I was using a copy of the same excel sheet which was much older and some changes I had made in it but your code is working like a perfect charm in the right sheet.

Cheers!!!

 

[sncb]

Hi Vog,

Its me again. I need to change a part of the code. Could you suggest what I need to do.
Do you remember this part of the code where column B was the unique identifier. Seems that 99% of the time it is unique but at times its duplicated with a record separator on column A. Column B was a transit no whereas Column A is a transit status.

So as an eg: in this case what is happening is that if yesterday Status C with Transit No 2957 was cut and moved to the Tab 'Hold' and today if these 2 records are created in Tab 'FollowUp" it does not move them to Tab 'Hold' and then need to be moved as well.

Status TransitNo
A 2957
B 2957


Could you suggest your recommended code change. Thanks once again Vog.

Try

Lastdate = Sheets("Test").Range("G" & Rows.Count).End(xlUp).Value
For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
    If IsError(Application.Match(Cells(intCounter, 2), Sheets("Test").Columns("B"), 0)) Then
        Rows(intCounter).Cut Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Sheets("Test").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Format(Date, "dd/mm/yyyy")
        Sheets("Test").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Date - Lastdate
    End If
End If
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete