Macro for Data Validation of Work Sheets

[jareddmccullough]

I have a data validation say in sheet "A" it currently operates of a set validation which references other sheet
names by me manually inputing references in a list say cell M:M of worksheet "A". This is creating some

difficulty because my list of worksheets keeps growing. My plan is to create a macro to operate this validation
within module (Worksheet) code. What I want it to do is collect all other titles except worksheet "A" into a

data validation. I feel this is a more simple task than what I am making it but seem to have a brain block at

the moment and need it pretty asap. So far I have come up with this. PLEASE HELP and thank you.

Private Sub Worksheet_Activate()
Dim ws As Worksheet, RNG As Range, r As Range, rCHCK As Range, buf As String
On Error Resume Next
For Each ws In Worksheets
If (ws.Name) Then
Next ws

Range("Z2").CurrentRegion.Sort Range("Z2"), xlAscending, Header:=xlNo
buf = Join(WorksheetFunction.Transpose(Range("Z:Z").SpecialCells(xlConstants)), ",")
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=buf
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("Z:Z").ClearContents
End Sub

I have used this code in other context but cant seem to figure out how to apply it.