I
i-Zapp
The intention of my macro is to put all the wb's sheet names into
named range as per the code below. Then, use the named range as th
list in a "data validation" cell, which will use the named range as th
list.
I've successfully been able to convert the array into a range, althoug
i can only populate the range horizontally (columns x 1 row). if i tr
to populate a vertical range, i get all the same value (the value o
index 1). I get similar results with the named range. And when I try t
use it in data validation, I get an error.
My workaround is to use the code to put the values into a horizonta
range, then use a named range to reference that range. Seems clunky an
unnecessary to include the intermediate step. Why not go from an arra
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 Su
named range as per the code below. Then, use the named range as th
list in a "data validation" cell, which will use the named range as th
list.
I've successfully been able to convert the array into a range, althoug
i can only populate the range horizontally (columns x 1 row). if i tr
to populate a vertical range, i get all the same value (the value o
index 1). I get similar results with the named range. And when I try t
use it in data validation, I get an error.
My workaround is to use the code to put the values into a horizonta
range, then use a named range to reference that range. Seems clunky an
unnecessary to include the intermediate step. Why not go from an arra
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 Su