Reference last worksheet & range in drop down list

[sfran]

Hello,

I have multiple worksheets in a workbook. Based on the criteria selected the multiple sheets can be created.
In one worksheet, I have a drop down box that is to show data (range B1:M1) from the last worksheet created. For example, the drop down list in cell B10 from sheet name "Feature 1" is to reference cells B1:M1 from the last worksheet.

I have tried multiple ways to try to accomplish this, but no luck. I am getting errors when I try to specify the last sheet in the validation formula... The code as I have it now is:

ActiveSheet.Range("B10").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=INDIRECT(" & Sheets(Sheets.Count).Name & ")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Any insight would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="='" & Sheets(Sheets.Count).Name & "'!B1:M1"

 

[sfran]

Works perfect!!!

Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.