Page Selection
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 ------------------------------------------------------------------------ Sagaron's Profile: http://www.excelforum.com/member.php...o&userid=24643 View this thread: http://www.excelforum.com/showthread...hreadid=382314 |
Page Selection
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" wrote in message ... 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 |
Page Selection
Brilliant - I can understand what you mean by tricky - thanks for you help -- Sagaro ----------------------------------------------------------------------- Sagaron's Profile: http://www.excelforum.com/member.php...fo&userid=2464 View this thread: http://www.excelforum.com/showthread.php?threadid=38231 |
Page Selection
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 |
All times are GMT +1. The time now is 01:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com