VBA Dynamic Range to Extend Rows
<p>I am trying to do what seems like a simple dynamic range task in VBA. </p>

<p><a href="https://i.stack.imgur.com/dB0c3.jpg" rel="nofollow"><img src="https://i.stack.imgur.com/dB0c3.jpg" alt="enter image description here"></a></p>

<p>The above table has data downloaded from an external website. New rows will be added to it every day. </p>

<p><a href="https://i.stack.imgur.com/x1mN7.png" rel="nofollow"><img src="https://i.stack.imgur.com/x1mN7.png" alt="enter image description here"></a></p>

<p>This second table references the first. So for example the Date column in this table will be =USA!A18.</p>

<p>I want to write a VBA script so that whenever new data is added to the first table, I can run the macro to extend down the rows/formulas of the second table to add the new data. </p>

<p>So if 5 new rows are added to the first table, the macro will extend the existing rows and formulas from columns A to W  in the second table down five. It cannot have any blank rows or #VALUE errors without breaking some other code in the spreadsheet. The data starts in A9 and extends to W9. </p>

<p>I tried implementing the following:</p>

<pre><code>Sub extendrows()

Dim lastRow As Long

lastRow = Worksheets("USA").Range("A" &amp; Rows.Count).End(xlUp)

Worksheets("USA defprob").Range("A9:W9").AutoFill Destination:=Range("A9:W" &amp; lastRow)

End Sub
</code></pre>

<p>but I get a memory error, which I suspect is because it's pasting several thousand blank rows instead of stopping. How can I adjust this code to do what I need the macro to do?</p>