Creating a back up when a certain range is reached?

[Simon Lloyd]

Hi all, i want to create a back up in of a workbook when a certain amount of data has been entered (the range A2:C4 is just for test purposes) ideally i want to make the back up when 3000 rows have been used and then delete all contents from A2 to C3000 then save, i'm having a bit of trouble sorting out how to find the active range and make the back up when it reaches 3000, can you help?

Here's what i have so far!

Simon

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim lStr_TargetFile As String
Dim sStr As String
Dim Range

With ThisWorkbook

If Range = Range("a2:C4") Then
Else
sStr = ThisWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, InStr(1, _
LCase(ThisWorkbook.Name), _
".xls") - 1) & _
" - " & Format(Now, "yyyymmdd") & ".xls"
End If

.SaveCopyAs sName
SetAttr sName, vbReadOnly
Range("A1:C5").Select
Selection.ClearContents
.Save

End With
End Sub

[Simon Lloyd]

I have made some headway, this works does everything i need except i cant incorporate the time in to the save format and when i open the back up copy it performs the same action which is unaceptable i only need this action to happen once from the original the back up does not need any code at all in it.....can you help?

Simon

Sub auto_open()
Dim sStr As String

If ActiveSheet.Rows.Count >= 6 Then

With ThisWorkbook

sStr = .Path & "\" & _
Left(.Name, InStr(1, _
LCase(.Name), _
".xls") - 1) & _
" - " & Format(Now, "ddmmyyyy", "hhmmss") & ".xls"
On Error Resume Next
.SaveCopyAs sStr
On Error GoTo 0
SetAttr sStr, vbReadOnly

range("A2:C5").Select
Selection.ClearContents
.Save

End With
End If
End Sub

[Simon Lloyd]

Sorry for the repost but i still cant get the back up copy to NOT make another copy when its opened..............does anyone know how to remove all code from a named workbook in VBA? so that i could incorporate that when it makes the back up copy it deletes all the code from the back up copy before it is saved!

Hope someone can help

Simon.