Hi

I have the following code to update a worksheet on a daily basis.

I would like to create a column with a row heading of Reviewed. in
column M.

This is fine but when I rerun the macro below new data overwrites this
column which will be left blank or have a Y for reviewed.

Code as follows

Sub TIPS()



ChDir "M:\Statdata"
Workbooks.OpenText Filename:="M:\Statdata\EDDTIPS.TXT",
Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)),
TrailingMinusNumbers:=True, _
Local:=True '<- this decides date interpretation





Range("A1:M500").Select
Selection.Copy

Workbooks.Open Filename:= _
"G:\Microbiology\Registrars\TIPSICU.xls ", Origin:=xlWindows
Sheets("Main").Select

Range("A65536").End(xlUp).Offset(1, 0).Select

ActiveSheet.Paste

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Columns("B:B").Select


Set Rng = ActiveSheet
R = 1
N = 1
With Rng
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Do While N <= LastRow
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R,
"#,##0")
End If

V = .Range("B" & R).Value
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to
'vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString
'explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction. _
CountIf(.Columns(1), vbNullString) 1 Then


.Rows(R).Delete
End If
Else
Next_V = .Range("B" & (R + 1)).Value
If V = Next_V Then
ThisDate = .Range("J" & R).Value


NextDate = .Range("J" & (R + 1)).Value
If ThisDate < NextDate Then
.Rows(R + 1).Delete
''? here

Else
.Rows(R).Delete
End If
Else
R = R + 1
End If
End If
N = N + 1
Loop
End With
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

'header:xlYes refers to the fact that there is a header row


ActiveWorkbook.Save

Windows("TIPSICU.xls").Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Windows("Macro.xls").Activate
Application.DisplayAlerts = False
Application.Quit



End Sub



Would anyone have any ideas on this one..

Many thanks
Eddie