printing an array of worksheets

[Guest]

I have an excel-file with a lot of worksheets in it, and I would like to
print only a few of them (in my case: from the first sheet to a sheet named
"btw").

I have the following code (selection made manually):

Sub TestPrint()
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Voorblad").Select
Sheets(Array("Voorblad", "Inhoudsopgave", "Opdracht", "res", "fin",
"liq", "balans", _
"V&W", "kosten", "Grondslagen", "vaste_act", "fin_act", "vl_act",
"liq midd", "ev", _
"pass lang", "pass kort", "btw")).Select
Sheets("Voorblad").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

But if there's a worksheet added, it will not be in the array.

And I have another code, selecting everything from the first sheet to the
sheet "btw", but it doesn't print the page-numbering allright (and with the
first code is does that allright):

Sub PrintenJaarrapport()

Dim S As Worksheet

For Each S In Worksheets
S.Select
If S.Name = "rekenblad" Then
Exit Sub
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Selection.PrintOut Copies:=1, Collate:=True
'ActiveWindow.SelectedSheets.PrintPreview
Next S
Worksheets("Inhoudsopgave").Activate

End Sub


Is there someone who can help me with this problem?

Thanks, Marco.

 

[Bernie Deitrick]

Marco,

Sub TestPrint2()
Dim i As Integer
For i = Sheets("Voorblad").Index To Sheets("btw").Index
Sheets(i).Select False
Next i
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hello Bernie,

it works perfect, when my cursor-position is on one of the sheets that has
to be selected. When the cursor is on one of the sheets that has not to be
printed, it adds that sheet also. Any idea how I can fix that?

Greetings, Marco.

 

[Bernie Deitrick]

Marco,

Add the line:

Sheets("Voorblad").Select

just after

Dim i As Integer

HTH,
Bernie
MS Excel MVP

 

[Guest]

It works, thanks!

Marco,

Add the line:

Sheets("Voorblad").Select

just after

Dim i As Integer

HTH,
Bernie
MS Excel MVP