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]

PM sent.

 

[VoG]

Try

Dim IntNoLinesFollowUp As Long, LastDate As Date
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 = Date
        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

 

[sncb]

Same error:

'Duplicate declaration in current scope'

but now pointing to:

IntNoLinesFollowUp

 

[VoG]

Try

Dim LastDate As Date
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 = Date
        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

 

[sncb]

Whew!!! that ran....But I guess I can check for new record creation only tomorrow and later. So I'll post accordingly.

No more words left to say Thanks.

 

[sncb]

This came out of the blue. On debugging I get this:

Runtime error 13

'Type Mismatch'



pointing to:

LastDate = Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value

 

[VoG]

Make sure that the bottom cell in column R contains an Excel date.

 

[sncb]

Don't know what exactly you mean by excel date, but I did enter today's date 22/03/2011 on cell R65536 but still gives the same error:

Runtime error 13

'Type Mismatch'

and point to the same line of code.

 

[VoG]

Try

If IsDate(Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value) Then
    LastDate = Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value
Else
    LastDate = Date
End If

 

[sncb]

happy to report that it worked with no debugging issues. now to wait till tomorrow when I run it again to see if it adds new records after the last one from today.

Sending you PM of the whole code for that step.

Thanks