Data validation with VBA error

[Mr2017]

Hi

I've written a macro which copies data from one sheet to another, then adds data validation in the last column.

However, I keep on getting a debug error when the code gets to the data validation part of the macro.

It says "Application-defined or object-defined error." It comes up after the ".delete" line in the code below.

Does anyone know why this may be? The data validation list is in cells B6 to B8 of Sheet 15 and should apply to cells S4:S100 in Sheet 4. I originally recorded the data validation steps to get the list of actions (please see below).

Thanks in advance.

Sub Columns()


Sheet15.Activate
Range("A1:R2").Copy
Sheet4.Activate
Range("B2").PasteSpecial (xlPasteAll)
Range("B2").End(xlToRight).Offset(1, 0).Select
    Range("S4:S100").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet15!$b$6:$b$8"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


Columns("S:S").AutoFit


End Sub

 

[Fluff]

What is the actual name (on the sheet tab) of the worksheet with the list?
It looks as though you are trying to use the sheet codename.

 

[Mr2017]

Hi Fluff

The name of the tab of the worksheet with the list is "Column Titles"

But, yes, I'd prefer to use the Sheet codename / "back of the house" instead of "front of the house" name, in case someone changes the "front of the house" name.

 

[Fluff]

In that case you will need to change the Sheet15 in the formula to Column Titles
Excel cannot see sheet code names.

 

[Fluff]

Another options is

Formula1:="='" & Sheet15.Name & "'!$b$6:$b$8"

 

[Mr2017]

Awesome!!

That worked!!

Thank you, Sir.

 

[Fluff]

You're welcome & thanks for the feedback