Overwrite data once certain value is reached

[Seeno]

Hi All,

I have some code which takes some data and then matches it to a specific column and then inputs that data on the next available row.

What I want to happen is that when the data gets to 8 rows, I would like it go back to the first row in that column and overwrite the data back to the 8 rows and then start again.

Here is the code I have so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("A2")) Is Nothing Then CopyRng
End Sub

Sub CopyRng()
Dim WS1 As Worksheet
Dim Rng As Range
Dim Col As Long
On Error Resume Next 'Without this macro crashes if there's no match
Set WS3 = Sheets("Sheet3")

Set Rng = WS3.Range("A6") 'column header

With WS3
'Finds the column to copy:
Col = Application.WorksheetFunction.Match(WS3.Range("A6").Value, .Rows("1:1"), False)

'Writes the values to the last empty cell from the bottom of the column:
.Cells(.Rows.Count, Col).End(xlUp).Offset(1, 0).Resize(Rng.Rows.Count).Value = Range("A2").Value

End With
End Sub

So this does what I want but instead of finding the next available row in that column, I would like to determine if there are already 8 lines of data and if there are overwrite the old data in line 1 and then continue.

Thanks
Matt

P.S I can't find how to add the code in the excel format like other posts

 

[JackDanIce]

Can't easily see issue with that line without your spreadsheet, however, try:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Cells(2, 1)) Is Nothing Then CopyRng

End Sub

Sub CopyRng()

    Dim wks As Worksheet
    Dim rng As Range
    Dim y   As Long
    
    On Error Resume Next
    Set wks = Sheets("Sheet3")
    If wks Is Nothing Then Exit Sub
    On Error GoTo 0
    
    Application.ScreenUpdating = False
    
    With wks
        y = .Cells(1, .Columns.count).End(xlToLeft).column
        Set rng = .Cells(1, 1).Resize(, y).find(what:=.Cells(6, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rng Is Nothing Then
            y = .Cells(.Rows.count, rng.column).End(xlUp).row + 1
            Set rng = .Cells(y, rng.column)
            rng.Value = .Cells(2, 1).Value
            If y > 9 Then
                .Cells(2, rng.column).Resize(x).ClearContents
                .Value = .Cells(2, 1).Value
                Set rng = Nothing
            End If
        End If
    End With
    
    Application.ScreenUpdating = True
    
    Set wks = Nothing
        
End Sub

Out of curiosity, what is the name of the sheet the worksheet_change event is being triggered from?