VBA script to copy cell on change

libussa

New Member
Joined
Sep 25, 2014
Messages
6
Hello everyone,
I know next to nothing about VBA, so I turn to you for my problem...

I have a web query that auto updates every X minutes, and dumps the results in cell C2, sheet s1.
I would like to keep all of the data to get a history of the changing values. So I would like to have script that copy C2 every time it changes, and paste the value in s2 in column A, for example. A nice addition would be to have a time stamp in the next column...

Thank you very much for your help !
 
Last edited:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
WELCOME TO THE FORUM

The sheet that the data is copied to is named as S2, change in the code as necessary

1. Right click Sheet S1-tab and select view code
2. Copy and paste this code in there

Everytime cell C2 on sheet S1 changes, you will see it updated in sheet S2 ( column A will have the value, column B will have the time stamp)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastrow As Integer
    Dim wks As Worksheet
    Set wks = Worksheets("s2")
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        lastrow = wks.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        wks.Range("A" & lastrow).Value = Target.Value
        wks.Range("B" & lastrow).Value = Time
    End If
End Sub
 

libussa

New Member
Joined
Sep 25, 2014
Messages
6
Thanks for your answer !
I tried to change the value in C2 by hand, then hit actualize to redo the query (changing C2), but nothing is copied...
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Please confirm that you right clicked S1, selected View code and pasted the code in there
I assume you already have a sheet called s2, it should throw up an error if you don't

I tested it and it seems to work fine
 

libussa

New Member
Joined
Sep 25, 2014
Messages
6

ADVERTISEMENT

ok so I closed and reopened Excel, accepted security warnings, and the script works fine...Except it's A1 that's being copied !
I don't see where in the script I should make a change though...

I should also mention that the web query occupies the range A1:C2, but I only want the value in C2
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
hmmmmmm. I tested also and found that it picks the value from the top-left cell from the query results which in your case in A1

If C2 was not a part of the query, it surely will work fine.

I will have to test and see if there's some way around it while also hoping that some excel guru will resolve it before I do :)
 

libussa

New Member
Joined
Sep 25, 2014
Messages
6

ADVERTISEMENT

Ok, what about doing a scheduled copy ? Same script, but copy C2 every X minutes...
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Copy C2 to what location and after how many minutes?
 

libussa

New Member
Joined
Sep 25, 2014
Messages
6
Same as before, copy C2 to s2, in a column to keep old data, with timestamp in next column
This could be done every 20 minutes
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
I just got an idea. SInce whenever c2 changes, it means A1 has changed, i changed the code to monitor A1 rather but in copy the value, it copies the actual value

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastrow As Integer
    Dim wks As Worksheet
    Set wks = Worksheets("s2")
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        lastrow = wks.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        wks.Range("A" & lastrow).Value = Target.Offset(1, 2).Value
        wks.Range("B" & lastrow).Value = Time
    End If
End Sub

Use this instead and see if that works for you
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,431
Members
409,876
Latest member
Akash Yadav
Top