Yes you're spot on with this. tbh, it shouldn't be there so I've removed. Benefit of fresh pair of eyes. :-)


you're copying from Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report") to x, x which is:
Set X = Sheets("Mainstay Report") of the active workbook before you added another workbook; that is the same sheet. So aren't you just converting a formula in cell B5 on that sheet to a plain value? Maybe that's the intention.

Ultimately what I'm trying to do here is to create a drop down list in cell 4 on the mainstay report (x.sheet). The list being created from a defined range (F4:F13) on the Mainstay master tab. (w.sheet) The range is to be called sitenames.

Spot on again. Didn't define the range. Doh. Obvious now.

I've amended the code to this instead. Removing the range and replacing with cells. It's solved the 'define app/object error' but inadvertently created a new error on the next line 'Invalid procedure'

I'm guessing this is because the range 'sitenames' has not been defined correctly?

X.Cells("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
Happy to speak to an expert. :-)

If you like I could send you a test file so you could try the code yourself. Least you wont be in the dark anymore. Either way I'm happy if it means we can resolve.

Also, just want to say a BIG thank you for helping me. 


This is so difficult to do in the dark.
If you want we could do a TeamViewer session tomorrow sometime (I'm in the UK)