Building an array of worksheets for printing
 

I have one array that I use for printing a subset of worksheets in a
workbook. Baseline array is defined as:

Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select

Depending on various Booleans I need to add other worksheets to the above.
Inclusion of these other worksheets is dependent on Booleans.

So if BoolBSIB is TRUE, then I need to add "PolicyValuesLedgerGuarGSIB", to
the above array BEFORE I get to the "PolicyValuesLedgerGuar", _

So it should read
Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuarGSIB", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select


I'm stymied on how to build up this array inside of VBA. There are three
other worksheets that may or may not get printed. How can i do this inside of
VBA?

I'd hate to brute force it by building each potential variation but...I'd
prefer a more elegant solution.

--
DRK

Building an array of worksheets for printing
 

Instead of an array have you considered using a collection instead, something
like

Dim MySheets As Collection

Sub addsheets()
Dim wks As Worksheet
Set MySheets = New Collection

MySheets.Add Sheet1, Sheet1.Name
MySheets.Add Sheet2, Sheet2.Name
For Each wks In MySheets
MsgBox wks.Name
Next wks

End Sub

You can add to the collection based on the booleans and then traverse the
collection in exactly the same manner as the worksheets object (which is
mostly just a collection of worksheets).
--
HTH...

Jim Thomlinson


"DRK" wrote:

I have one array that I use for printing a subset of worksheets in a
workbook. Baseline array is defined as:

Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select

Depending on various Booleans I need to add other worksheets to the above.
Inclusion of these other worksheets is dependent on Booleans.

So if BoolBSIB is TRUE, then I need to add "PolicyValuesLedgerGuarGSIB", to
the above array BEFORE I get to the "PolicyValuesLedgerGuar", _

So it should read
Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuarGSIB", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select


I'm stymied on how to build up this array inside of VBA. There are three
other worksheets that may or may not get printed. How can i do this inside of
VBA?

I'd hate to brute force it by building each potential variation but...I'd
prefer a more elegant solution.

--
DRK

Building an array of worksheets for printing
 

Jim - I'll give it a shot tomorrow. I've been here since 6 AM, it's now after
7PM and I can't see straight anymore. I'll be back in by 6 AM.

Thanks
--
DRK


"Jim Thomlinson" wrote:

Instead of an array have you considered using a collection instead, something
like

Dim MySheets As Collection

Sub addsheets()
Dim wks As Worksheet
Set MySheets = New Collection

MySheets.Add Sheet1, Sheet1.Name
MySheets.Add Sheet2, Sheet2.Name
For Each wks In MySheets
MsgBox wks.Name
Next wks

End Sub

You can add to the collection based on the booleans and then traverse the
collection in exactly the same manner as the worksheets object (which is
mostly just a collection of worksheets).
--
HTH...

Jim Thomlinson


"DRK" wrote:

I have one array that I use for printing a subset of worksheets in a
workbook. Baseline array is defined as:

Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select

Depending on various Booleans I need to add other worksheets to the above.
Inclusion of these other worksheets is dependent on Booleans.

So if BoolBSIB is TRUE, then I need to add "PolicyValuesLedgerGuarGSIB", to
the above array BEFORE I get to the "PolicyValuesLedgerGuar", _

So it should read
Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuarGSIB", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select


I'm stymied on how to build up this array inside of VBA. There are three
other worksheets that may or may not get printed. How can i do this inside of
VBA?

I'd hate to brute force it by building each potential variation but...I'd
prefer a more elegant solution.

--
DRK

Building an array of worksheets for printing
 

Two techniques, either of which you can adapt to your specific needs.

sheets(1).select
sheets(3).select false
to select the first and the 3rd sheet in the workbook

or, the following selects every alternate sheet in the workbook.

Option Explicit

Sub PrintMultiSheets()
Dim x(), i As Integer
ReDim x(0)
With ActiveWorkbook.Sheets
For i = 1 To .Count Step 2
x(UBound(x)) = .Item(i).Name
ReDim Preserve x(UBound(x) + 1)
Next i
End With
ReDim Preserve x(UBound(x) - 1)
Sheets(x).Select
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have one array that I use for printing a subset of worksheets in a
workbook. Baseline array is defined as:

Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select

Depending on various Booleans I need to add other worksheets to the above.
Inclusion of these other worksheets is dependent on Booleans.

So if BoolBSIB is TRUE, then I need to add "PolicyValuesLedgerGuarGSIB", to
the above array BEFORE I get to the "PolicyValuesLedgerGuar", _

So it should read
Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuarGSIB", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select


I'm stymied on how to build up this array inside of VBA. There are three
other worksheets that may or may not get printed. How can i do this inside of
VBA?

I'd hate to brute force it by building each potential variation but...I'd
prefer a more elegant solution.

Building an array of worksheets for printing
 

As a point of interest would there be any advantage to using the this code
over creating a collection? I just find collections kinda handy for this sort
of thing, but I am always open to suggestions.
--
HTH...

Jim Thomlinson


"Tushar Mehta" wrote:

Two techniques, either of which you can adapt to your specific needs.

sheets(1).select
sheets(3).select false
to select the first and the 3rd sheet in the workbook

or, the following selects every alternate sheet in the workbook.

Option Explicit

Sub PrintMultiSheets()
Dim x(), i As Integer
ReDim x(0)
With ActiveWorkbook.Sheets
For i = 1 To .Count Step 2
x(UBound(x)) = .Item(i).Name
ReDim Preserve x(UBound(x) + 1)
Next i
End With
ReDim Preserve x(UBound(x) - 1)
Sheets(x).Select
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have one array that I use for printing a subset of worksheets in a
workbook. Baseline array is defined as:

Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select

Depending on various Booleans I need to add other worksheets to the above.
Inclusion of these other worksheets is dependent on Booleans.

So if BoolBSIB is TRUE, then I need to add "PolicyValuesLedgerGuarGSIB", to
the above array BEFORE I get to the "PolicyValuesLedgerGuar", _

So it should read
Sheets(Array("Cover", "AboutThisIllustration", "PolicyValuesLedger", _
"PolicyValuesLedgerGuarGSIB", _
"PolicyValuesLedgerGuar", _
"PolicyValuesLedgerNonGuar")).Select


I'm stymied on how to build up this array inside of VBA. There are three
other worksheets that may or may not get printed. How can i do this inside of
VBA?

I'd hate to brute force it by building each potential variation but...I'd
prefer a more elegant solution.

Building an array of worksheets for printing
 

In article ,
says...
As a point of interest would there be any advantage to using the this code
over creating a collection? I just find collections kinda handy for this sort
of thing, but I am always open to suggestions.

Because Sheets({collection variable}).Select doesn't work? ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions