Macro to Print several worksheets

[ulfah]

I am using Excel 2000 SP-3 and Windows XP.

I am recording a macro to print a specific, selected area (say B47)
in All _-Visible-_ Worksheets.
I have 13 Sheets, all with specific names, always the same name, always
in the same order. The first and the last Sheets should
_always_be_excluded_ from being printed.
Any number of the remaining 11 sheets could be hidden, except 3! So I
have always between 3 - 11 sheets to print, never knowing how many or
which one is the last (of the visible) sheets.

I record this operation as follows;
I hide "the First" and "the Last" sheet, I Select the 2nd Sheet (now
the first of the visible sheets), then Select B47, I right click the
sheet tab and choose Select All Sheets and define the print format of
the selection. Then Print and Print selection.
When still having all visible sheets selected I select "A1" (to clean
up all views).
Finally unhide "the First" and "the Last" sheets.
I select "the First" sheet and select "A1", to be at the starting point
again.

When _-Recording-_ this sequence it works perfectly!! I get all visible
worksheets printed, with the choosen format !!

But when _-Running_the_macro-_ I only get the first of the visible
sheets printed!

Can anyone tell me why this happens??

Many thanks in advance
Ulf

 

[Dave Peterson]

Is it ok to send multiple print jobs?

Option Explicit
Sub testme()
Dim NamesToExclude As Variant
Dim wks As Worksheet
Dim AddrToPrint As String

NamesToExclude = Array("sheet1", "sheet2")
AddrToPrint = "B4:d7"

For Each wks In ThisWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
If IsNumeric(Application.Match(wks.Name, NamesToExclude, 0)) Then
'skip it
Else
wks.Range(AddrToPrint).PrintOut preview:=True
End If
End If
Next wks

End Sub

 

[ulfah]

Thanks very much for your help.
That was a very elegant solution, and I made it work!
As you understand I am not very experienced with VB. I usually build my
macros on basis of a recording and from there I try to add on solutions
by trial and error.
As i will try to add Format to your solution as well as different
Ranges and different Layouts.
Thanks again for your support.
Ulf