Printing Worksheets (Array)
 

Need some help printing Worksheets. I have a Workbook that can contain
varying amount of Worksheets. Currently I count the sheets of a particular
type and based on that count (x) I store the sheet.names in strings s1 to
s10 then select the Case below based on x:

Select Case X
Case 1
Worksheets(Array(s1, "Terms1", "Terms2")).Select
Case 2
Worksheets(Array(s1, s2, "Terms1", "Terms2", ss)).Select
Case 3
Worksheets(Array(s1, s2, s3, "Terms1", "Terms2", ss)).Select
Case 4
Worksheets(Array(s1, s2, s3, s4, "Terms1", "Terms2", ss)).Select

Currently I have this built out to 10 and works fine but the requirement
has grown and I hate to use this long method.

Is there a way to automatically build this Array, i.e., if x was 25, it
would automatically build the above from S1 to S25?

Or is this just a stupid way of doing this and there is a much easier way
to print the sheets you want?

Thanks, Rob

Printing Worksheets (Array)
 

dim v() as Variant
redim v(0 to 0)
for each sh in worksheets
' the condition you are checking for would be in
' the next line where the example check for "Report" is
' located
if sh.Range("A1").Value = "Report"
v(ubound(v)) = sh.name
redim preserve v(0 to ubound(v)+1)
end if
Next
Redim Preserve v(0 to Ubound(v) - 1)
worksheets(v).Printout

--
Regards,
Tom Ogilvy


"RobC" wrote:

Need some help printing Worksheets. I have a Workbook that can contain
varying amount of Worksheets. Currently I count the sheets of a particular
type and based on that count (x) I store the sheet.names in strings s1 to
s10 then select the Case below based on x:

Select Case X
Case 1
Worksheets(Array(s1, "Terms1", "Terms2")).Select
Case 2
Worksheets(Array(s1, s2, "Terms1", "Terms2", ss)).Select
Case 3
Worksheets(Array(s1, s2, s3, "Terms1", "Terms2", ss)).Select
Case 4
Worksheets(Array(s1, s2, s3, s4, "Terms1", "Terms2", ss)).Select

Currently I have this built out to 10 and works fine but the requirement
has grown and I hate to use this long method.

Is there a way to automatically build this Array, i.e., if x was 25, it
would automatically build the above from S1 to S25?

Or is this just a stupid way of doing this and there is a much easier way
to print the sheets you want?

Thanks, Rob

Printing Worksheets (Array)
 

Tom,

Thanks... Works Perfectly and as a plus if someone now looks at my code
they may even think I know what I'm doing...

Thanks again,
Rob

?B?VG9tIE9naWx2eQ==?= wrote in
:

dim v() as Variant
redim v(0 to 0)
for each sh in worksheets
' the condition you are checking for would be in
' the next line where the example check for "Report" is
' located
if sh.Range("A1").Value = "Report"
v(ubound(v)) = sh.name
redim preserve v(0 to ubound(v)+1)
end if
Next
Redim Preserve v(0 to Ubound(v) - 1)
worksheets(v).Printout