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:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
Copy C2 to what location and after how many minutes?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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