Copy & Paste to an Archive

  • Thread starter Thread starter Donnie Stone
  • Start date Start date
D

Donnie Stone

Still need some help on...

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

When I run the macro below, 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....
 
See answer in previous thread.

--
Regards,
Tom Ogilvy


Donnie Stone said:
Still need some help on...

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

When I run the macro below, 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
Click to expand...
Click to expand...
 
Back
Top