Just make it a 'vertical' array to start with:

Sub updatesheets()
Dim Sht As Worksheet
Dim N As Integer
ReDim m(1 To ActiveWorkbook.Worksheets.Count, 1 To 1)

For Each Sht In ThisWorkbook.Worksheets
N = N + 1
m(N, 1) = Sht.Name
Next Sht
Range("a1").Resize(N).Value = m
End Sub


--
Jim
"i-Zapp" wrote in
message ...
|
| 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
|