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):
Any help would be greatly appreciated!
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
Last edited by a moderator: