I don't think you can set a Validation on a Range, you have to do it on a single Cell.
You could write a function that does it for a cell and pass in a range to do it to each one.


Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set range1 = ws.Range("A1:A5")
Set rng = ws.Range("B1")

With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws.Name & "'!" & range1.Address
End With

Where Range1 is the set of data to show in your dropdown.
rng is the cell you want the dropdown on.

---
You don't have to select the range, just use that range against the #.Validation.
---

Loop through the sheets:

For Each sh In ActiveWorkbook.Sheets
    MsgBox sh.Name
Next sh

So if you want to check if you're on the last one:

Sheets(Sheets.Count).Name


You could have an IF statement

If Sh.Name <> "A" Then
  'Do something
End If

or

If Sh.Name = "A" Then
  'Skip
Else
  'Do something
End If


Notes
Say you open Excel, by default there are 3 worksheets - Sheet1, Sheet2, Sheet3, you can rename these and re-order them.
For example I've renamed mine "A", "B" and "C".
When you open up the VBA editor the Microsoft Excel Objects show
Sheet1 (A)
Sheet2 (B)
Sheet3 (C)
This is because the Sheet has a (Name) and a Name.
You could reorder these sheets in the workbook and rename them but their (Name) is still Sheet1/2/3 but 1=B 2=A 3=C so be careful to use the correct Name, or if you are using the index check it is the correct one first.