Page Selection

[Sagaron]

Hi
I would like the result in a cell to select the array of sheets. For eg, if cell A1 on sheet1 = "sheet1","sheet2","sheet3", I would like all 3 sheets to be selected. So far I have tried:
Sheets(Array(Sourceprint)).Select

with Sourceprint being the name defined for that cell (in this case A1 on sheet1) which gives a error

thanks
Ron

[Jim Cone]

Ron,

It's a little tricky.
The Split function returns an array, however
it is not available in Excel 97.
'-----------------------
Sub TestSelection()
Dim strNames As Variant
strNames = Range("Sourceprint").Value
strNames = Split(strNames, ",", -1, vbTextCompare)
Sheets(strNames).Select
End Sub
'---------------------

Jim Cone
San Francisco, USA


"Sagaron" <[email protected]> wrote in
message news:[email protected]...
Hi
I would like the result in a cell to select the array of sheets. For
eg, if cell A1 on sheet1 = "sheet1","sheet2","sheet3", I would like all
3 sheets to be selected. So far I have tried:
Sheets(Array(Sourceprint).Select

with Sourceprint being the name defined for that cell (in this case A1
on sheet1) which gives a error
thanks
Ron

[Sagaron]

Brilliant - I can understand what you mean by tricky - thanks for your help!

[Dave Peterson]

Another suggestion (based on one of your previous posts is to build that array
of worksheet names and then use that:

Option Explicit
Private Sub UserForm_Initialize()
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Me.ListBox1.AddItem wks.Name
Next wks

Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim Result() As String
Dim i As Long
Dim j As Long

ReDim Preserve Result(0 To Me.ListBox1.ListCount - 1)

j = -1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
j = j + 1
Result(j) = ListBox1.List(i)
End If
Next i

If j = -1 Then
'do nothing, nothing selected
Else
ReDim Preserve Result(0 To j)
ActiveWorkbook.Worksheets(Result).PrintOut
End If

Unload Me

End Sub




Sagaron wrote:
>
> Brilliant - I can understand what you mean by tricky - thanks for your
> help!
>
> --
> Sagaron
> ------------------------------------------------------------------------
> Sagaron's Profile: http://www.excelforum.com/member.php...o&userid=24643
> View this thread: http://www.excelforum.com/showthread...hreadid=382314

--

Dave Peterson