Hi,

I am unfortunatly not very proficient in Excel. I have created a
simple web query that refreshes every minute. However, what I would
like it to do is to save after each refresh is to "time stamp" and go
to the next available line where the next refrehable data would go
until I finally press the break button.

Example.

- 1st data returned starts at cell A1 to F15.
- Then a time stamp in cells G1 to G15.
- Save
- On the next refresh, the next available cell ( A16 ) is used etc
etc.

Any help would be greatly appreciated.

Thanks.

Why try to move the web query? Just move the data instead. The example
below uses the 'Worksheet_Change' event of the sheet with the web query to
trigger a copy/paste operation that copies the updated data to the clipboard
and then pastes it at the end of the data on another sheet. The routine also
adds a time stamp in the next available column. You will probably have to
modify it a little to suit your needs. I assumed that the sheet you save the
data on will be named "Saved Data".

Instructions: Right-click on the worksheet tab of the sheet with the web
query. Select "View Code". Copy the code below and paste it into the Visual
Basic Editor window. The code will run every time your web query updates the
data.

HTH,

Eric

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Dim wsCurrent As String, wsName As String

wsCurrent = ActiveSheet.Name
Me.Select ' Select the sheet with the change event
wsName = Me.Name ' Save the sheet name

Set dataRange = ActiveWorkbook.Worksheets(wsName).Range("A1:F15")
'
' If the data in our target block changed, do the
' stuff below.
'
If (Not Intersect(Target, dataRange) Is Nothing) Then
Application.ScreenUpdating = False
Application.EnableEvents = False
dataRange.Select
Selection.Copy
ActiveWorkbook.Worksheets("Saved Data").Select
ActiveSheet.Cells(ActiveSheet.Rows.Count,
"A").End(xlUp).Offset(1, 0).Select
Selection = dataRange
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Resize(15, 1).Select
ActiveCell.Value = Now() ' Apply a time stamp to the data
Selection.FillDown
ActiveWorkbook.Worksheets(wsCurrent).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
'
End Sub


"qcan" wrote:

Hi,

I am unfortunatly not very proficient in Excel. I have created a
simple web query that refreshes every minute. However, what I would
like it to do is to save after each refresh is to "time stamp" and go
to the next available line where the next refrehable data would go
until I finally press the break button.

Example.

- 1st data returned starts at cell A1 to F15.
- Then a time stamp in cells G1 to G15.
- Save
- On the next refresh, the next available cell ( A16 ) is used etc
etc.

Any help would be greatly appreciated.

Thanks.

On Jul 8, 6:03*pm, EricG wrote:
Why try to move thewebquery? *Just move the data instead. *The example
below uses the 'Worksheet_Change' event of the sheet with thewebqueryto
trigger a copy/paste operation that copies the updated data to the clipboard
and then pastes it at the end of the data on another sheet. *The routine also
adds a time stamp in the next available column. *You will probably have to
modify it a little to suit your needs. *I assumed that the sheet you save the
data on will be named "Saved Data".

Instructions: *Right-click on the worksheet tab of the sheet with thewebquery. *Select "View Code". *Copy the code below and paste it into the Visual
Basic Editor window. *The code will run every time yourwebqueryupdates the
data.

HTH,

Eric

Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim dataRange As Range
* * Dim wsCurrent As String, wsName As String

* * wsCurrent = ActiveSheet.Name
* * Me.Select ' Select the sheet with the change event
* * wsName = Me.Name ' Save the sheet name

* * Set dataRange = ActiveWorkbook.Worksheets(wsName).Range("A1:F15")
'
' If the data in our target block changed, do the
' stuff below.
'
* * If (Not Intersect(Target, dataRange) Is Nothing) Then
* * * * Application.ScreenUpdating = False
* * * * * * Application.EnableEvents = False
* * * * * * * * dataRange.Select
* * * * * * * * Selection.Copy
* * * * * * * * ActiveWorkbook.Worksheets("Saved Data").Select
* * * * * * * * ActiveSheet.Cells(ActiveSheet.Rows.Count,
"A").End(xlUp).Offset(1, 0).Select
* * * * * * * * Selection = dataRange
* * * * * * * * ActiveSheet.Paste
* * * * * * * * ActiveCell.Offset(0, 6).Resize(15, 1).Select
* * * * * * * * ActiveCell.Value = Now() *' Apply a time stamp to the data
* * * * * * * * Selection.FillDown
* * * * * * * * ActiveWorkbook.Worksheets(wsCurrent).Select
* * * * * * Application.EnableEvents = True
* * * * Application.ScreenUpdating = True
* * End If
'
End Sub



"qcan" wrote:
Hi,

I am unfortunatly not very proficient in Excel. I have created a
simplewebquerythat refreshes every minute. However, what I would
like it to do is to save after each refresh is to "time stamp" and go
to the next available line where the next refrehable data would go
until I finally press the break button.

Example.

- 1st data returned starts at cell A1 to F15.
- Then a time stamp in cells G1 to G15.
- Save
- On the next refresh, the next available cell ( A16 ) is used etc
etc.

Any help would be greatly appreciated.

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much Eric. It works flawlessly !!!