How to access muliple tabs of sheet as variable of an Array
 

I created a file with multiple Worksheets and defined an Array:

Dim arrRtTab(1 To 12) As String

arrRtTab(1) = "MS"
arrRtTab(2) = "MW"
arrRtTab(3) = "MM"
arrRtTab(4) = "MU"
arrRtTab(5) = "MDS3"
arrRtTab(6) = "XS"
arrRtTab(7) = "XN"
arrRtTab(8) = "QN"
arrRtTab(9) = "QE"
arrRtTab(10) = "QS"
arrRtTab(11) = "NS"
arrRtTab(12) = "LI"

Depending on results of my computations I need to be able select
multiple tabs of the file and format or print them.
How to build the variable to be able to select tabs for instance MS,
MM, XN or QS, LI for printing them?

Please Help

Georgee

How to access muliple tabs of sheet as variable of an Array
 

Perhaps a UserForm could solve your problem?


Sub ShowDialog()

UserForm1.Show

End Sub

Code for the UserForm:

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must choose at least one sheet - or click x"
Exit Sub
End If
ThisWorkbook.Worksheets(arr).PrintOut
Unload Me
Sheets(1).Select
End Sub

Private Sub UserForm_Initialize()
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub

"GorKo" wrote in message
oups.com...
I created a file with multiple Worksheets and defined an Array:

Dim arrRtTab(1 To 12) As String

arrRtTab(1) = "MS"
arrRtTab(2) = "MW"
arrRtTab(3) = "MM"
arrRtTab(4) = "MU"
arrRtTab(5) = "MDS3"
arrRtTab(6) = "XS"
arrRtTab(7) = "XN"
arrRtTab(8) = "QN"
arrRtTab(9) = "QE"
arrRtTab(10) = "QS"
arrRtTab(11) = "NS"
arrRtTab(12) = "LI"

Depending on results of my computations I need to be able select
multiple tabs of the file and format or print them.
How to build the variable to be able to select tabs for instance MS,
MM, XN or QS, LI for printing them?

Please Help

Georgee

How to access muliple tabs of sheet as variable of an Array
 

Thanks for offerd help but, I think that you are offering me a way to
manually enter a tab while I need a way to have tabs selected
automatically depending on the values in certain cells

Georgee

Alf Bryn wrote:
Perhaps a UserForm could solve your problem?


Sub ShowDialog()

UserForm1.Show

End Sub

Code for the UserForm:

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must choose at least one sheet - or click x"
Exit Sub
End If
ThisWorkbook.Worksheets(arr).PrintOut
Unload Me
Sheets(1).Select
End Sub

Private Sub UserForm_Initialize()
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub

"GorKo" wrote in message
oups.com...
I created a file with multiple Worksheets and defined an Array:

Dim arrRtTab(1 To 12) As String

arrRtTab(1) = "MS"
arrRtTab(2) = "MW"
arrRtTab(3) = "MM"
arrRtTab(4) = "MU"
arrRtTab(5) = "MDS3"
arrRtTab(6) = "XS"
arrRtTab(7) = "XN"
arrRtTab(8) = "QN"
arrRtTab(9) = "QE"
arrRtTab(10) = "QS"
arrRtTab(11) = "NS"
arrRtTab(12) = "LI"

Depending on results of my computations I need to be able select
multiple tabs of the file and format or print them.
How to build the variable to be able to select tabs for instance MS,
MM, XN or QS, LI for printing them?

Please Help

Georgee