The intention of my macro is to put all the wb's sheet names into a
named range as per the code below. Then, use the named range as the
list in a "data validation" cell, which will use the named range as the
list.

I've successfully been able to convert the array into a range, although
i can only populate the range horizontally (columns x 1 row). if i try
to populate a vertical range, i get all the same value (the value of
index 1). I get similar results with the named range. And when I try to
use it in data validation, I get an error.

My workaround is to use the code to put the values into a horizontal
range, then use a named range to reference that range. Seems clunky and
unnecessary to include the intermediate step. Why not go from an array
to a "n x 1" named range (vs a "1 x n")?



Code:
--------------------
Sub updatesheets()

Dim m()
ReDim m(ActiveWorkbook.Sheets.Count)

For Each sht In ThisWorkbook.Sheets
m(n) = sht.Name
n = n + 1
Next sht

ActiveSheet.Range("a1").Resize(1, (n - 1)).Value = m

End Sub
--------------------


--
i-Zapp
------------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php...fo&userid=5768
View this thread: http://www.excelforum.com/showthread...hreadid=479125