I have an array in Excel that I currently have hard coded. I need to make this more dynamic in nature as the array size and value are not constant. This array consist of the names of worksheets in a workbook. The intent of the array is to set the order of the worksheets.
My current code looks something like this:
Dim arSheets() as Long
Dim intWSCount as Integer
dim intLastSheet as Integer ' keep track of which sheet in the array was sorted last.
ReDim arSheets(1 to intWSCount)
arSheets(1) = "The first sheet" ' This text is the sheet name
arSheets(2) = "The second sheet"
' and so on through the total sheets in the workbook. Note that arSheets(0) is excluded
' as the first sheet will remain first.
intLastSheet = 1 ' set at 1 to start with array position 1.
For I = 0 To UBound(arSheets)
intLastSheet = 1 + I
The question that I have is how can I assign a sheet quickly to an array position without having to type "arSheets(1) = "The first sheet" for each item. In my current code I have to renumber the array if I inserted a new sheet between the current arSheets(1) and arSheets(2) - meaning that arSheets(2) would become arSheets(3), and so on. This is tedious if there are a lot of elements in the array (the current worksheet has 38 sheets to be sorted.
This VBA code takes a bunch of CSV files and imports them into one workbook. Ultimately I want to display a list of the sheets in a dialog box and allow the user to reorder them as they want (thinking of something like this: http://www.dailydoseofexcel.com/archives/2004/06/14/move-upmove-down-in-a-listbox/
). Maybe it would be the shortest route to the desired goal to build this functionality first then order the sheets from the list order.
Any thoughts would be appreciated.