A timed macro to copy data.

Hewlett-packard Hp g60 notebook pc
April 9, 2009 at 05:04:32
Specs: Microsoft Windows Vista Home Premium, 2 GHz / 3002 MB
I need to create a macro for excel that copies data in specific cells in Sheet1 to the next empty column in Sheet2. The data I am copying is coming from a web query that updates every hour, so I would like this copy to happen when the data is updated so as to keep a history of these values. How would I go about doing this?

See More: A timed macro to copy data.

Report •


#1
April 9, 2009 at 08:37:30
I don't do anything with web queries so I have to ask a question first. Does the query update trigger the Change event? If so, you could use the Worksheet_Change event which would run whenever a change was made to the cells that get updated:

I'm making 2 assumptions in my example:

1 - The range that gets updated is A1:A5
2 - There is already data in Row 1 of Sheet2, which is the row the code uses to find the last used column.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
  nxtCol = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column + 1
  Range("A1:A5").Copy Destination:=Sheets(2).Cells(1, nxtCol)
 End If
End Sub


Report •

#2
April 9, 2009 at 11:04:13
I don't believe it triggers the change event. Is there some other way to monitor the values and copy the data if it is changed? I have very little experience creating macros, so any help will be appreciated.

Report •

#3
April 9, 2009 at 12:02:49
re: "I don't believe it triggers the change event."

Well, that's kind of an important piece of information to know and worth testing.

If the Change event is triggered, then we've found the solution.

If not, a "time-based" macro is possible, but I'd rather see the code triggered by an actual Event as opposed to depending on a clock which may or may not coincide with the update, etc. I just see more potential problems with 2 independent operations, one assuming the other has happened, as opposed to the first one actually causing the second.

A simple test would be this:

Right click the sheet tab for the sheet that does the query and choose View Code. Paste this into the window that opens. If the change event is triggered, the MsgBox will pop up and we'll have our answer.

Important Note! Where the code says "A1:A5", you need to put in the actual range that gets updated, or at least some range of cells that you know will change.

Another Important Note! You may want to back up your original spreadsheet in case something goes terribly wrong. Better yet, create a new workbook, paste the code in and run a query or 2 to see if the Change Event is triggered.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
     MsgBox "Update Triggered Change Event!"
 End If
End Sub


Report •

Related Solutions


Ask Question