Macro to Print several worksheets

  • Thread starter Thread starter ulfah
  • Start date Start date
U

ulfah

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

I am recording a macro to print a specific, selected area (say B4:D7)
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 B4:D7, 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
 
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
 
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
 
Back
Top