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

 

[VoG]

Hi. Try like this

Application.CutCopyMode = False
For intCounter = 2 To intNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
    Rows(intCounter).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next intCounter

 

[sncb]

nops..that didn't work...any more info you would need?

 

[VoG]

Error, nothing happened, ...?

 

[sncb]

no..nothing happened..i named the new sheet as 'Test' and renamed the 'Sheet2' to 'Test' in the code

the process ran like usual but the removed records did not show up in the 'Test' sheet

 

[VoG]

This worked for me. The sheet with No in column F must be selected before running

Sub atest()
Dim intCounter As Long, IntNoLinesFollowUp As Long
IntNoLinesFollowUp = 16
For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
    Rows(intCounter).Copy Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next intCounter
End Sub

 

[sncb]

here is the full original code..I believe the last line is not relevant to this process, but the next....just assuming..could be wrong


intNoLinesFollowUp = WorksheetFunction.CountA(Sheets("FollowUp").Range("A:A"))
intCounter = 0

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
Next intCounter
Range("A1").Select

 

[VoG]

As I said, the code I posted worked for me.

 

[sncb]

Thanks man//it worked...

 

[sncb]

Hi Vog,

Thanks for earlier help.

However one question..after changing the logic from deleting to making a copy in the new 'test' sheet, the original records in the 1st sheet still exist. I tried changing the copy to cut but its leaving the rows blank. Is there a way to remove the blank rows after they are removed from the original sheet.

Thanks once again
​