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=574145