Excel copy data from one sheet to another on worksheet refresh with vba
<p>I'd like to copy data from 'Sheet1' in a specific cell to the next available empty cell in 'Sheet2' such that changes (F9) result in data being copied from Sheet1 and a columne of data being populated in Sheet2.</p>

<p>So far I have the below, which throws an error around: <code>(.Rows.Count</code></p>

<p>Is it possible to popluate a column in Sheet2 every time the workbook is refreshed on 'F9'?</p>

<pre><code>'In this example I am Copying the Data from Sheet1 (Source) to Sheet2              (Destination)
Private Sub worksheet_calculate()
'Method 2
'Copy the data
Sheets("Sheet1").Range("I1").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Sheet2.Cells(.Rows.Count, "A").End(xlUp).Row
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
</code></pre>