vba update cell with data validation?
<p>I have 2 worksheets, Product Groups and Database:</p>

<p>My sheet, Product Groups has a layout like below:</p>

<pre><code>A4
A5      {Button}
A6
A7
A8
</code></pre>

<p>A user can add values to column A4 onwards. Meaning this list could extend to A4- A8 or A4 to A100.</p>

<p>I have created a button that allows the user to run a macro after they have updated the values in column A.</p>

<p>This macro should insert the values from A4 to Last used row into my data validation in Cell T7 on my database sheet.</p>

<p>Here is my code:</p>

<pre><code>Sub button()
Dim lastRow As Long
lastRow = ThisWorkbook.Worksheets("Product Groups").Range("A4" &amp; Rows.Count).End(xlUp).Row


Dim ws As Worksheet
Dim ws2 As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Database")
Set ws2 = ThisWorkbook.Worksheets("Product Groups")


Set rng = ws.Range("T7")
Set range1 = ws2.Range("A1:A" &amp; lastRow)

With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" &amp; ws2.Name &amp; "'!" &amp; range1.Address
End With


End Sub
</code></pre>

<p>For some reason i get an error on this line:</p>

<pre><code>lastRow = ThisWorkbook.Worksheets("Product Groups").Range("A4" &amp; Rows.Count).End(xlUp).Row
</code></pre>

<p>Application undefined or object undefined error.</p>

<p>Please can someone show me where this is going wrong? THanks</p>