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]

Try like this

For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
    Rows(intCounter).Cut Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[sncb]

Thanks Vog..it works like a charm...ure a genius man

I however need to add just one last detail. on the 'test' sheet, on let's say cell G2 and all rows below till the last record, I need to have the macro run date and no of days since it last ran on H2 and all records below.....the no of days since last run is not very imp but the run date definitely is..

Any suggestions...Thanks once again..

 

[VoG]

Try

For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" 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")
End If
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[sncb]

Hi Vog,

your code did add the date but only on the first row (in cell G2) but not for any other records below...

 

[VoG]

I've just run it and it did

Excel Workbook
	A	B	C	D	E	F	G
2	10	1	4	4	8	No	18/03/2011
3	7	9	8	5	5	No	18/03/2011
4	10	4	7	10	10	No	18/03/2011
Test

 

[sncb]

Oh yeah..it did run...but for some reason not all the way through to the last record..stopped somewhere in between ..but all in all THANKS a million Vog....logging off for the day... salute!!

 

[sncb]

Hi Vog,

could you explain what would be the meaning of this part of the code:

intNoLinesFollowUp = 16
For intCounter = 2 To intNoLinesFollowUp

Thanks

 

[VoG]

It loops through column F from rows 2 to 16.

 

[sncb]

got it. Thanks..

if you remember in the new tab 'Test' in column G we had the run date, in which case I see today's date with lets say 10 records create(G2 to G11)

But let's say tomorrow I return and run the macro again I need the newest records created from G12 onwards with tomorrow's date beside it but only if they do not occur in G2-G11 from previous runs...(is this even possible?)

Finally in column H, what code do I need to add to have the no of days since the last run was made...

Thanks a lot Vog..uve been really supportive.

 

[VoG]

For the first question, is there a unique identifier in any column that we could check in both sheets?

For the second try

Lastdate = Sheets("Test").Range("G" & Rows.Count).End(xlUp).Value
For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" 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
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete