Copy Data to Rows instead of Columns
 

Thanks for the response, I appreciate it, but I still need help.

When I run the macro, it only copies and paste data in Column A only. Here
is the layout for Sheet1 that is being copied and pasted to the DATA
worksheet. The data is being provided by a web query and is being refreshed
every x minutes and resides in A2:H2.

A B C D E F G H
1 UP DW PT HX MA VE MI XY
2 1 2 3 4 5 6 7 8

The layout for the DATA worksheet is the same. As the macro runs, it should
copy data from Sheet1 and paste it to the DATA worksheet creating an archive
of the data that was input in A2:H2 in Sheet1.

A B C D E F G H
1 UP DW PT HX MA VE MI XY
2 1 2 3 4 5 6 7 8
3 1 6 78 78 78 89 6 32
4
5

and so on....

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

Copy Data to Rows instead of Columns
 

public NextTime as Date
Sub PasteToArchive()

Dim sourceRange As Range
Dim destRange As Range
Set sourceRange = Sheets("SHEET2").Range("A2:H2")
Set destRange = Sheets("DATA").Cells(rows.count,1).End(xlup)(2) _
.Resize(1,8)
destRange.Value = sourceRange.Value
'code to run macro every ? minutes based on "DATA" A24
NextTime = now + 1/1440*Worksheets("sheet2").Range("M1")
Application.OnTime NextTime, "PasteToArchive"

End Sub

Adjust the calculation of NextTime to reflect when you want to do it. You
were multiplying by D1, but now that holds one of your labels.

--
Regards,
Tom Ogilvy


Donnie Stone wrote in message
...
Thanks for the response, I appreciate it, but I still need help.

When I run the macro, it only copies and paste data in Column A only.
Here
is the layout for Sheet1 that is being copied and pasted to the DATA
worksheet. The data is being provided by a web query and is being
refreshed
every x minutes and resides in A2:H2.

A B C D E F G H
1 UP DW PT HX MA VE MI XY
2 1 2 3 4 5 6 7 8

The layout for the DATA worksheet is the same. As the macro runs, it
should
copy data from Sheet1 and paste it to the DATA worksheet creating an
archive
of the data that was input in A2:H2 in Sheet1.

A B C D E F G H
1 UP DW PT HX MA VE MI XY
2 1 2 3 4 5 6 7 8
3 1 6 78 78 78 89 6 32
4
5

and so on....

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