Excel 2010 VBA stops at Row 164

clqwe111

New Member
Joined
Sep 18, 2014
Messages
2
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):
Code:
'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!
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
This line:
Code:
Range("R1" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("K13").Value
should be:
Code:
Cells(Rows.Count, "R").End(xlUp).Offset(1, 0).Value = Range("K13").Value
and similarly for your column Y line.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top