VBA to copy a drop down list to the last used row but not the values in it.

[justme101]

Hi all,

Scenario: I have some data in a sheet, the data for which gets updated fairly regularly. The new data gets appended after the current last used row. I have a lot of formulae in various columns, and I can successfully copy and apply the all till the end of the data, after the data gets updated. The issue is with one column which as drop down list in the first cell (J10). It has 3 values in it, Yes, No and (blank).

Issue: when I use the following lines of code to get the lastrow and copy the drop down list till the last used row of data, it overwrites all the values in between, with whatever is in the 1st cell (J10). So, let's say value for J10 is manually set to No by the user, J11 is Yes, J12 is Yes, when I use this code, it populates every cell till the end with No.

Requirement: I need the VBA to just include the drop down list to newly added rows, where the drop down will not automatically come up. It should not overwrite any existing cells.

Sub update_formula()

ThisWorkbook.Worksheets("Master Tracker").Activate

Dim lastrow3 As Long
Application.ScreenUpdating = False

lastrow3 = Range("A" & Rows.Count).End(xlUp).Row

Range("J10").Copy
Range("J10:J" & lastrow3).PasteSpecial xlPasteFormulas

End sub