Excel 2010 VBA stops at Row 164

[clqwe111]

Hi, I am new to this forum and Vba. I inserted a Data Connection from a webpage table that updates every minute. My vba code copies a particular cell from this changing table to a new table on the same sheet. I actually do this for two cells to create two new tables. The code also inputs a time stamp each time the data refreshes. The tables continue to add a row each minute until it reaches row 164, then it stops. I need to continue the tables until it reaches row 1000. Here is the code (sorry for the comments they are for me):

'The following line ensures the code below is for this sheet only
Private Sub Worksheet_Change(ByVal Target As Range)

'This code copies the dynamically changing cell K13 to column R and drops down a row (eg: R1) each time K13 changes
    If Not Intersect(Target, Range("K13")) Is Nothing Then
        Range("R1" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("K13").Value
    End If

'This code copies the dynamically changing cell K14 to column Y and drops down a row (eg:Y1) each time K14 changes
    If Not Intersect(Target, Range("K14")) Is Nothing Then
        Range("Y1" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("K14").Value
    End If

'This code adds the current time in 3rd column after R (eg:T) and drops down a row after each entry.
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("r3:r1000")) Is Nothing Then
            With Target(1, 3)
                .Value = Time
                .EntireColumn.AutoFit
            End With
        End If

'This code adds the current time in 3rd column after Y (eg:AA) and drops down a row after each entry.
     If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("Y3:Y1000")) Is Nothing Then
            With Target(1, 3)
                .Value = Time
                .EntireColumn.AutoFit
            End With
        End If

End Sub

Any help would be greatly appreciated!

 

[RoryA]

This line:

Range("R1" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("K13").Value

should be:

Cells(Rows.Count, "R").End(xlUp).Offset(1, 0).Value = Range("K13").Value

and similarly for your column Y line.

 

[clqwe111]

Works great! Thank you