Print all workbooks macro

beca

New Member
Joined
May 17, 2011
Messages
36
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 Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

EXCEL MAX

Well-known Member
Joined
Nov 11, 2020
Messages
508
Office Version
  1. 2016
Platform
  1. Windows
Hello Beca,
try in this way...
VBA Code:
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
 
Last edited:

HaHoBe

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
Hi

shouldn´t it be
Code:
ActiveSheet.Printout
instead of
Code:
vWS.PrintOut
Ciao,
Holger
 

EXCEL MAX

Well-known Member
Joined
Nov 11, 2020
Messages
508
Office Version
  1. 2016
Platform
  1. Windows
O, yes...
Thans for correction...
 

beca

New Member
Joined
May 17, 2011
Messages
36

ADVERTISEMENT

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

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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

New Member
Joined
May 17, 2011
Messages
36

ADVERTISEMENT

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

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
Hi beca,

as every worksheet is inserted as a copy before sheets(1) just change the codeline
Code:
    wks.Copy Before:=Sheets(1)
to
Code:
    wks.Copy After:=Sheets(Sheets.Count)
to add them after the last sheet.

Ciao,
Holger
 
Solution
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top