Print all workbooks macro

[beca]

Hi I have a code to remove the cell highlights before printing for a worksheet as below:

Sub ClearPrint()
Sheets("Worksheet 1").Copy Before:=Sheets(1)
With Sheets(1).Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Sheets(1).PrintOut
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True
End Sub

How do I modify the code to print multiple worksheets in one print with the same requirements above (i.e. remove cell highlights before printing).

Thank you.

 

[EXCEL MAX]

Hello Beca,
try in this way...

Sub ClearPrint()
   
    Dim vWS As Worksheet
   
    Application.DisplayAlerts = False
    For Each vWS In ActiveWorkbook.Worksheets
        vWS.Copy Before:=Sheets(1)
        With ActiveSheet.Cells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ActiveSheet.PrintOut
        ActiveSheet.Delete
    Next vWS
    Application.DisplayAlerts = True
   
End Sub

 

[HaHoBe]

Hi

shouldn´t it be

ActiveSheet.Printout

instead of

vWS.PrintOut

Ciao,
Holger

 

[EXCEL MAX]

O, yes...
Thans for correction...

 

[beca]

Hi Holger, the code works perfectly, but it prints the worksheets separately, rather than one print that prints all the worksheets.

Is it possible to have the code to print all sheets in one go? so the printed pdf has multiple pages.

 

[HaHoBe]

Hi beca,

you could use a select to group the worksheets or collect the names of the sheets in an array and print and delete the sheets at the end.

Try the following code which is based on the second idea:

Sub ClearPrint_2()
  
  Dim wks As Worksheet
  Dim arrstrSelect() As Variant
  Dim lngCount As Long
  
  Application.DisplayAlerts = False
  For Each wks In ActiveWorkbook.Worksheets
    wks.Copy Before:=Sheets(1)
    With ActiveSheet
      With .Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      ReDim Preserve arrstrSelect(0 To lngCount)
      arrstrSelect(lngCount) = .Name
      lngCount = lngCount + 1
    End With
  Next wks
  
  With Sheets(arrstrSelect)
    .PrintOut
    .Delete
  End With
  
  Application.DisplayAlerts = True
  
End Sub

Ciao,
Holger

 

[beca]

Hi Holger, the code works perfectly, but it prints the worksheets in reverse order, i.e. worksheet 3 first, then worksheet 2, and finally worksheet 1.

Any idea how to fix?

 

[HaHoBe]

Hi beca,

as every worksheet is inserted as a copy before sheets(1) just change the codeline

    wks.Copy Before:=Sheets(1)

to

    wks.Copy After:=Sheets(Sheets.Count)

to add them after the last sheet.

Ciao,
Holger

 

[beca]

Works like magic. Thank you Holer