Separating items in a list 2

[Zenkai]

I have a set of worksheets, all listed in a single cell. Like this:

"Sheet1, Sheet2, Sheet3 . . . "

And in VB, I want to select all of those sheets. I WAS using sheets(array("Sheet1", "Sheet2", . . . ).select

But now I want to use sheets(array(SheetNames)).select

Where SheetNames is the value in the cell as written above. How could I make that work? The number of sheets is not constant. I do not want to separate the list into separate cells.

-Nick

 

[Zenkai]

I should probably rewrite what I have above, as it looks a little ambiguous:

I have a set of worksheets, and they are listed in a single cell. Like this:

A1 = "Sheet1, Sheet2, Sheet3"

And in VB, I want to select all of those sheets. I WAS using:

sheets(array("Sheet1", "Sheet2", "Sheet3").select

But now I want to use"

sheets(array(A1.value)).select

How could I make that work? It currently won't work because it needs an actual comma (rather than the comma contained in the string) to work. The number of sheets is not constant, and I do not want to separate the list into separate cells.

-Nick

 

[PHV]

Have you tried this ?
sheets(Split(A1.value, ",")).select


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Hi

Use the Split Function

sh = Split([A1], ",")
For i = LBound(sh, 1) To UBound(sh, 1)
    MsgBox Trim(sh(i))
Next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[roccorocks]

So, do the worksheets exist in the workbook already? Does the cell just represent certain sheets that are to be selected, at one point in time? If not, then you can use this code to select each sheet in the workbook. I have not fully tested this, so I am not sure if all sheets will stay selected. Let me know.

For X = 1 To wrkExcel.Worksheets.Count
  Set shtExcel = appExcel.Worksheets.Item(X)
  shtExcel.select
Next X

If you want to select all the sheets specified in the cell, use the split function of VB or my code below.

shtexcel.value is the cell you want to get info from, or use a variable.

arecord = shtexcel.value
Do
  If InStr(arecord, ",") <> 0 Then
    aSheetName = Left(arecord, InStr   (arecord, "|") - 1)
      theLength = theLength - Len(aSheetName) - 1
        If theLength <> -1 Then
          arecord = Right(arecord, theLength)
            sheets(x) = Trim(aSheetName)
        End If
  Else
    aSheetName = Left(arecord, theLength)
    sheets(x) = Trim(aSheetName)
    exit do
  End If
Loop

or like

Do
  If InStr(arecord, ",") <> 0 Then
    aSheetName = Left(arecord, InStr   (arecord, "|") - 1)
      theLength = theLength - Len(aSheetName) - 1
        If theLength <> -1 Then
          arecord = Right(arecord, theLength)
            Set shtExcel = appExcel.Worksheets.Item(trim(aSheetName))
            shtExcel.select

        End If
  Else
    aSheetName = Left(arecord, theLength)
    Set shtExcel = appExcel.Worksheets.Item(trim(aSheetName))
    shtExcel.select
    exit do
  End If
Loop

Let me know how this works.

Rocco

 

[Zenkai]

Thanks! Still not quite working yet . . .

Also, PHV's code doesn't quite work. I get an "Out of range" error. It apparently can't find the sheets as a split.

-Nick

 

[SkipVought]

You need to Trim() each element of the array



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

OR...

Try this

Sheets(Split([A1].Value, ", ")).Select

thats COMMA SPACE




Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Hi Skip.
I'm afraid the Split function want an empty or single character string as delimiter, so I suggest this:
Sheets(Split(Replace([A1].Value, ", ", ","), ",")).Select

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

PHV,

My 2000 version does it with a number of different delimiter combinations in addition to as posted above
", "
",:"
", :"

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Skip, you're absolutely right.
Yet another broken help page