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]

Hi Vog,

So here's what has happened. In column 'G' I get today's date but in 'H' I get 3/22/2011

So here's how it looks:

G H
22/03/2011 3/22/2011

 

[VoG]

Sorry, it is obviously not picking up the correct value for Lastdate but I don't know why. I think you'll have do do without this 'feature'. It should be easy enough to spot the number of days since the last run by scanning column G.

 

[sncb]

Yeah, i guess Ill do without it. I'll just look at the dates.

But regarding adding new records to the tab 'Test' for new dates like today after the last record of the previous date, that's not happening.

What I'm doing is opening the original document and running it for today due to which I'm getting only today's records. If I use yesterday' saved document it dosen't update records from today. It just stays the same as if nothing happened.

Something not related directly to this: This macro code has about 5 steps of which this is step 4. For example if I need to add another step between 4 and 5 but without writing code and just doing the process itself(like recording the macro) whats the way to proceed?

Thanks once again.

 

[VoG]

Don't forget that we added code to prevent the same record being transferred again.

 

[sncb]

Hi Vog,

I get it.

But I need to add new records that ran today after the last record for the previous day and so on and so forth. That's not happening. Any suggestions?

Current code:

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")

End If
End If
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[VoG]

I made a small change

Sub atest()
Dim IntCounter As Long, IntNoLinesFollowUp As Long, LastDate As Date
IntNoLinesFollowUp = 16
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
End Sub

My results: I added the row in blue to ensure I had a date in column G. Then I ran the code. Then I ran the code again (row in red). These results look correct to me.

Excel Workbook
	A	B	C	D	E	F	G	H
1	1	1	1	1	1	1	14/03/2011
2	48	17	7	37	53	No	22/03/2011	8
3	53	68	44	84	2	No	22/03/2011	8
4	66	29	34	32	63	No	22/03/2011	8
5	39	88	98	63	44	No	22/03/2011	0
Test

 

[sncb]

I copied the code exactly and I get the following compile error

'Duplicate declaration in current scope'

pointing to:

IntCounter As Long

 

[VoG]

Can you post your code - ALL of it.

 

[sncb]

Is there a way to send via PM?

 

[VoG]

Just click anywhere in the code window, press CTRL + A, then CTRL + C, then come here and press CTRL + V.