The code below allows me to copy data from Sheet2, B1:B8, and paste it to
DATA, B1:B8, C1:C8 and so on until I get to column IV.

What I want to do is change the location of the data in Sheet2 to A2:H2 and
I would like the macro to paste the data to DATA, A2:H2, A3:H3 and so on.

I'm also using the Application On Time Now to run the macro every x minutes.
I would like this macro to run following the web query refresh. Is this
possible?

Thanks,

Donnie

Sub PasteToArchive()

Dim sourceRange As Range
Dim destRange As Range
Set sourceRange = Sheets("SHEET2").Range("B1:B8" & Range( _
"B" & Rows.Count).End(xlUp).Row)
Set destRange = Sheets("DATA").Range("IV1").End(xlToLeft).Offset( _
0, 1).Resize(sourceRange.Count, 1)
destRange.Value = sourceRange.Value
'code to run macro every ? minutes based on "DATA" A24
Application.OnTime Now + 1 / 1440 * Sheets("Sheet2").Range("D1"),
"PasteToArchive"

End Sub

This should do it..

You need to cancel the ontime procedure before closing the workbook.
which is why you need to store the next runtime in a variable and
include the closeevent

Alternatively you could use the change_event on the query's worksheet
(which will be triggered by when the query refreshes) to trigger this
archive routine.

suc6

Option Explicit

Public dRuntime As Date

Sub Archive()
Dim rSrc As Range, rTgt As Range
Set rSrc = Range([Sheet1!b1], [sheet1!b65000].End(xlUp))
Set rTgt = Worksheets("sheet2").UsedRange.EntireRow
Set rTgt = rTgt.Offset(rTgt.Rows.Count).Resize(1, rSrc.Rows.Count)
rTgt.Value = WorksheetFunction.Transpose(rSrc.Value)
dRuntime = Now + 1 / 1440 * [Sheet2!D1]
Application.OnTime dRuntime, "Archive"

End Sub

'Code for thisworkbook
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dRuntime, "Archive", , False
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Donnie Stone" wrote:

The code below allows me to copy data from Sheet2, B1:B8, and paste it
to DATA, B1:B8, C1:C8 and so on until I get to column IV.

What I want to do is change the location of the data in Sheet2 to
A2:H2 and I would like the macro to paste the data to DATA, A2:H2,
A3:H3 and so on.

I'm also using the Application On Time Now to run the macro every x
minutes. I would like this macro to run following the web query
refresh. Is this possible?

Thanks,

Donnie

Sub PasteToArchive()

Dim sourceRange As Range
Dim destRange As Range
Set sourceRange = Sheets("SHEET2").Range("B1:B8" & Range( _
"B" & Rows.Count).End(xlUp).Row)
Set destRange = Sheets("DATA").Range("IV1").End(xlToLeft).Offset(
_
0, 1).Resize(sourceRange.Count, 1)
destRange.Value = sourceRange.Value
'code to run macro every ? minutes based on "DATA" A24
Application.OnTime Now + 1 / 1440 * Sheets("Sheet2").Range("D1"),
"PasteToArchive"

End Sub