First, I never saw a response where you said that printing the array of sheets
made one file. Did you test it?

Second...

You can drop almost all the "redim preserve" statements.

This is better:

Dim SheetNames() as string
....other dim statements.
redim sheetnames(1 to thisworkbook.sheets.count)

You'll still keep track of the sctr stuff (sctr = SheetCounter). But at the
end, your print routine changes to:

if sctr 0 then
'chop the elements that weren't used
redim preserve sheetnames(1 to sctr)
Sheets(SheetNames).PrintOut preview:=True
end if


Dave Peterson wrote:

The simple answer first (I think!).

If the way the worksheet with the checkbox gets included is through one of those
loops, you can avoid that worksheet by looking at the name.

For Each wks In ThisWorkbook.Worksheets
if wks.name = me.name then
'do nothing
else
If wks.Range("B71").Value < "" Then
....

==========

If you always have worksheets named B1 to B17, you could use:

dim wCtr as long 'near the top with the other Dim Statements
....

for wctr = 1 to 17
set wks = worksheets("B" & wctr)
if wks.name = me.name then
'do nothing
else
if wks.range("b17").value < "" then
...
end if
next wctr

You won't loop through all the worksheets--you'll just look at B1 to B17.

I'm not sure what you mean by the worksheets that print by default. If you mean
that they always print, you could use:

'say you have 3 sheets that always print
dim Sheetnames(1 to 3) as string
...

sheetnames(1) = "default#1"
sheetnames(2) = "default#2"
sheetnames(3) = "default#3"

'so the rest of the code won't change
sctr = ubound(sheetnames) - lbound(sheetnames) + 1

========

If you mean that you already have some sheets added, but you MAY want to print
mo


'add the "default" sheets he
sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#1"

sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#2"

sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#2"

'then do your looping
for wctr = 1 to 17
set wks = worksheets("B" & wctr)
if wks.name = me.name then
'do nothing
else
if wks.range("b17").value < "" then
...
end if
next wctr

===============
Since you know how many you're adding, you could replace this:

'add the "default" sheets he
sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#1"

sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#2"

sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = "default#2"

with

'add the "default" sheets he
ReDim Preserve SheetNames(1 To sCtr + 3)
sctr = sctr + 1
SheetNames(sCtr) = "default#1"
sCtr = sCtr + 1
SheetNames(sCtr) = "default#2"
sCtr = sCtr + 1
SheetNames(sCtr) = "default#2"

This avoids a few "redim preserve"'s that aren't really necessary.



Aaron wrote:

That code is great and it works but your code and your comments have made me
realize I need to change my approach.

I need to change the code checking cell B71 to only check sheets "B1"
through "B15".

Also in the section of code you wrote on this how can I include some sheets
that need printed by default. There are default sheets that should be
printed in order both before and after the sheets being evaluated in for
content in cell B71.

Also, do you have any ideas on how to keep the sheet with all the check
boxes from being printed.

Thank you,

Aaron

"Dave Peterson" wrote:

Did you test that .printout line to see if you really got a single file. If you
got multiple files, then you're wasting your time with this approach!

Since you're building an array of sheet names, you could check to see if the
worksheet name is already in the array.

Dim Res as variant
'you only need to declare wks once and you can reuse it in both loops
'you don't need sh or she
dim wks as worksheet

.....lots of code

For Each wks In ThisWorkbook.Worksheets
If wks.Visible = -1 Then
If wks.Range("B71").Value < "" Then
if sctr = 0 then
'no names in array yet, so just add it
sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = wks.name
else
'there are names in the array, so check to see if that one is
'is in the array
res = application.match(wks.name, sheetnames,0)
if isnumeric(res) then
'already there, so don't add it again
else
'not there, so add it.
sCtr = sCtr + 1
ReDim Preserve SheetNames(1 To sCtr)
SheetNames(sCtr) = wks.name
end if
end if
end if
end if
next wks


and you'd still print with something like:

If sCtr 0 Then
Sheets(SheetNames).PrintOut preview:=True
End If


Aaron wrote:

So I think I have it for the most part.

Most of the code is for printing the report or the proposal for some
financial work I do.

There are 2 sections in the original code I posted where it is selecting
sheets to be printed for technical review based on the content of the sheets.
These are to be printed in addition to default sheets to be printed. How do
I revise this to your suggested code to get the correct effect?

If CheckBox31 = True Then
Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut

'xlSheetVisible = -1
Dim she As Worksheet
For Each she In ThisWorkbook.Worksheets
If she.Visible = -1 Then
If she.Range("B71").Value < "" Then
she.PrintOut
End If
End If
Next she

Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut
End If

<<snipped


--

Dave Peterson

--

Dave Peterson