Maybe I didn't explain everything fully. I have data comeing into row 3
automatically throught an opc server. I have vba written so that
everytime new data comes in on row three the old data keeps dropping
down through the rows. Well as everyone knows excell starts to get
sloggish with more and more data comeing in. What I would like to do is
save the entire workbook, under todays date, at a certain time of the
day. Then on the origional workbook clear all the data from rows 4 and
down. Then on the next day at the preset time save the workbook again
with the date. Can someone add to my code to have this possible? I've
enclosed the code below..
Thanks,
Mike


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A3").Value < 1 Then
Exit Sub
End If
If Not Intersect(Range(Target.Address), Me.Range("A3")) _
Is Nothing Then
Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column)
_
.End(xlUp).row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4:H4").Value = Range("A3:H3").Value
Range("C4").Value = Now
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
vaOldValues = Me.Range("A4:H" & _
IIf(iLastRow = 4, 5, iLastRow))
Range("A5:H5").Resize(UBound(vaOldValues, 1), 6).Value = _
vaOldValues
Range("A4:H4").Value = Range("A3:H3").Value
Range("C4").Value = Now
Set rgOldValues = Me.Range(Cells(Target.row + 2, Target.Column), _
Cells(iLastRow, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If
Exit Sub
End Sub


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=573762