Following macro copies data from a row of a range every 05 seconds and pastes it to another range in the next empty row (in top to down sequence).
The row no. from which the data is to be copied is picked up from cell L1 and the row no. increases dynamically with a simple count function on the spreadsheet.
The macro works fine when Sheet1 (on which the copy - paste functions are supposed to run) is the currently the active sheet. However, if the currently active sheet or workbook is different from Book1.xlsm - Sheet1, then the macro starts copying data from Row1 of the range (ignoring the row no. from cell L1).
Need help to modify the macro to pick up the row no. from cell L1 of Book1.xlsm-Sheet1 even if the currently active workbook or worksheet is different.
Option Explicit Dim TimeToRun As Date Dim Test1 As Range Sub Home_Test() With Workbooks("Book1.xlsm").Sheets("Sheet1") Set Test1 = .Range("M" & Range("L1").Value & ":" & "V" & Range("L1").Value) Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Test1.Value TimeToRun = Now + TimeValue("00:00:05") Application.OnTime TimeToRun, "Home_Test" End With End Sub
Note: Working sample file attached. It can be tested by starting the macro and keeping another worksheet active or opening another worksheet in a new window and keeping that active.