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.




    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


    The dot is what ties the statement to whatever follows the With statement. Otherwise will refer to whatever is active, as you have discovered.

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


    Hi Naira


    Because it's within the With statement, then this line can be done like this


    Code
    .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Test1.Value

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!