Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy formulas from rows & columns | Excel Worksheet Functions | |||
Can't Copy/Cut Cells, Rows or Columns | Excel Discussion (Misc queries) | |||
copy cells from columns to rows | Excel Discussion (Misc queries) | |||
Copy values in columns to rows | Excel Worksheet Functions | |||
Copy Data to Rows instead of Columns | Excel Programming |