Macro not picking up row no. from Worksheet cell for copy paste of dynamic range

Hi,

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.

Re: Macro not picking up row no. from Worksheet cell for copy paste of dynamic range

Ah!
The missing dot problem surfaces again. Was struggling with it just last week here: http://www.ozgrid.com/forum/sh…92247&p=733538#post733538

Thanks for your reply StephenR (works great)