I am using a macro to create a temporary sheet in my workbook, populate a bunch of cells on that sheet with data, export that sheet to a new workbook and close the new workbook.

This all works fine. What I am trying to do is add a drop down list to the temporary sheet (and, hence, new workbook) which holds the list of cell values that were added to said sheet. So I googled how to do this and came across this code:

Sub main()

'replace "J2" with the cell you want to insert the drop down list
With Range("J2").Validation
.Delete
'replace "=A1:A6" with the range the data is in.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, Formula1:="=Sheet1!A1:A6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub 

I incorporated that into my project, made the necessary reference changes, and tried it out, but when I open the new workbooks the drop down menu is not there. Any suggestions as to how to get this to work?

CodePudding user response：

Range("J2").Validation references the active sheet when you run the code - you should use an explicit reference like e.g. wsTemp.Range("J2") where wsTemp is the sheet you added before.

Furthermore: you have to copy Sheet1 as well to the new workbook - as you take the validation list values from there. Copy this sheet before you copy the temporary sheet - to be on the safe side.