Excel: adding worksheets to an array progessively

[Aeneas]

I am adding worksheets and assigning them a name according to some base list in the original worksheet. This will vary every time, or from time to time, so I need to create a way to explicitly create an array of the new sheets each time without hard-coding.

Easier to understand as follows: essentially the type of code I want to execute goes something like this:


Dim SheetList as Variant
Dim LastRow as Long

Dim Symbol as String


SheetList = Array("Blank&quot

Sheets("Blank&quot.Select
LastRow = Range("A65536&quot.end(xlUp).Row

For i = 1 to LastRow

Symbol = Range("B" & i).Value
Sheets.Add
ActiveSheet.Name = Symbol

<potential further code omitted>

SheetList = SheetList union Array(Symbol)

Next i

<now one can work with the array of all sheets that were added>



This would essentially be the same as just looping through the blank sheet and trying to generate the array without even adding the new worksheets every time, but I wanted to give context. The &quot;expression.Union(Arg1, Arg2, ...)&quot; in the help file applies to ranges only, not arrays of sheetnames or strings or whatever.

Please let me know if one has any insights. Thanks in advance!


Cheers,

Aeneas

 

[RoyVidar]

I'm not quite sure what you're trying to achieve, but having the sheets names both in a sheet, and as actual sheets, might become troublesome. What if code excecution halts in the midle of your crating routine and the new sheet is created, but the name not saved?

There's a easy to use way of collecting all sheet names from the sheets collection. You might consider using this in stead.

This sample prints all the sheets in activeworkbook to the immidiate pane:

sub test()
dim ws as worksheet
for each ws in activeworkbook.worksheets
debug.print ws.name
next ws
end sub

This is absolutely dynamic.

HTH Roy-Vidar

 

[Aeneas]

Specifically, I have a workbook with one sheet called &quot;Blank&quot;. I import a text output from another program which is a list of, say, people for a team.

I have the person's name, initials, jersey number, position, etc. in a grid. Copy that data to the blank page. Close the text file. Then loop from 1 to LastRow in the Excel File on the blank page (I am looping through my potential team members here). I want to create a new worksheet for each member either by initials or jersey number or whatever.

So in my code, I have gotten this far.


What I want to end up with at the end of this is a variable (variant) that = Array(&quot;14&quot;,&quot;00&quot;,&quot;31&quot;,...,&quot;n&quot

representing the jersey numbers, or = Array(&quot;TB&quot;,&quot;AC&quot;,&quot;GK&quot;,...,&quot;n&quot representing initials.

This array also represents the sheetnames since I named sheets by jersey number or intitals.


So, at this point, I just want an array of my team. How do you add items to an array, remove items from an array, or union one array to another?

I suppose in the end I want to try to:

Sheets(SheetList).copy

where SheetList = Array(&quot;14&quot;,&quot;00&quot;,&quot;31&quot;,...,&quot;n&quot

This allows me to work with them as a group instead of having to loop through each one. For printing this is OK, but I am looking for potential further application, such as this copy procedure, etc.


Hope this helps explain it better.


Cheers,

Aeneas

 

[RoyVidar]

OK

To make an one dimensional array to perform this:

sub test
dim lLast as long
dim lCounter as long
dim arr() as string

llast=range(&quot;a65536&quot.end(xlup).row
redim arr(llast-1) ' sets the upper limit of array
for lcounter=0 to llast-1
arr(lcounter)=cells(lcounter,0).value
next lcounter
end sub

You can declare arrays in any datatype, and also multidimensional, but one and the same array can only contain one datatype.

HTH Roy-Vidar

 

[RoyVidar]

Oups
arr(lcounter)=cells(lcounter+1,1).value

But I haven't the foggiest about how you're going to achieve what you wan't thru this approach, to be frank. But good luck.

Roy-Vidar

 

[SkipVought]

Hi,

You do not need an array. You already have your data in an array of sorts, a Collection of cells in worksheet Blank. Here's what you can do...

set wsBlank = activesheeet
with wsBlank.Cells(1,1).currentregion
  for r = 1 to .rows.count
    worksheets.add
    for c = 1 to .columns.count
      select case c
      case 1  'name
        activesheet.name = wsBlank.Cells(r, c).value
      case 2  'initials
        'stuff to do with initials
      case 3  'jersey number
        'stuff to do with jersey number
      case 4  'position
        'stuff to do with position
      end select
    next
  next
end with

If your data has headings, naturally you'd have to start with row 2.

But that's 'bout all it takes

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Aeneas]

This is how I ended up executing it (it was actually blocks of data, not a nice easy table):


Do until check=true

Sheets(&quot;Blank&quot.Select
StartRow = Range(&quot;A&quot; & LastRow).End(xlDown).Row
If StartRow = 65536 Then
Check = True
Exit Do
End If
LastRow = Range(&quot;A&quot; & StartRow).End(xlDown).Row
LastCol = Range(&quot;A&quot; & StartRow).End(xlToRight).Column

Symbol = UCase(Range(&quot;A&quot; & StartRow).Offset(-3,1).Value)

If ArrayCount = 0 Then
SheetList = Array(Symbol)
Else
ReDim Preserve SheetList(ArrayCount)
SheetList(ArrayCount) = Symbol
End If
ArrayCount = ArrayCount + 1

Sheets.Add
ActiveSheet.Name = Symbol

<code omitted>

loop

Sheets(SheetList).Copy




Now that last line works. Thanks for all your help!!!