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.
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.