Simple Macro to Print Preview All active tabs

[Peyton]

Morning,

I need help with a simple macro to print preview all visible tabs in my excel workbook and print them double sided. There are over 30 tabs in the workbook. I created an ActiveX command button and can only get the print preview to work if i don't hide any tabs. Below is what i am using. I would like to add to it so it will automatically print double sided after the first sheet.


Thanks in advance.

Peyton

 

[vletm]

A sample file is missing.

 

[Peyton]

I don't have a sample file but I do have the code I am using. Currently the code is running each tab in print preview mode so it takes for ever to print the entire book. I was also hoping to add a code to double sided print to save paper.

Sub Print_All()
Dim sht
Application.ScreenUpdating = False
For Each sht In Sheets
If sht.Visible And sht.Name <> "Input Sheet" Then
With sht
.PrintPreview
'.PrintOut copies:=1
End With
End If
Next
Application.ScreenUpdating = True
End Sub

Does this help? I am currently stuck. Thanks

 

[vletm]

I find out that it's much simple to set printer manually!
You could test something like this.

There are two dialogs to test:
Application.Dialogs(xlDialogPrint).Show
Application.Dialogs(xlDialogPrinterSetup).Show
I couldn't test 2nd Dialog ... I don't use Windows!

Sub Print_All()
'   set manually double sided printing
''    Application.Dialogs(xlDialogPrint).Show
    Application.Dialogs(xlDialogPrinterSetup).Show
   
    Application.ScreenUpdating = False
    tbl_max = Application.Worksheets.Count
    For tbl = 1 To tbl_max
        Worksheets(tbl).Activate
        If ActiveSheet.Visible and _
           ActiveSheet.Name <> "Input Sheet" Then _
               ActiveSheet.PrintOut copies:=1
    Next tbl

'   => reset manually double sided printing
''    Application.Dialogs(xlDialogPrint).Show
    Application.Dialogs(xlDialogPrinterSetup).Show
End Sub

 

[Peyton]

Thanks! I will give this a try.

 

[Peyton]

Is there a way to tweak this current macro to make the print preview flow without looking at each tab. I want to run the macro to show print preview for tabs at one time. Basically without the macro I hold shift and click on as many tabs as I want and press print preview to view all them at once. Please let me know what I am missing. Thanks!

Private Sub Print_All_Click()
 
  Dim sht
  Dim ShtName
  ShtName = Array("Input Sheet")
For I = 1 To Worksheets.Count
  For J = 0 To UBound(ShtName)
  If Worksheets(I).Name = ShtName(J) Then Count = 1
  Next J
  If Count = 0 Then Worksheets(I).PrintPreview
  Count = 0
Next I
End Sub

 

[vletm]

I cannot use/test 'PrintPreview',
but I find some possible instructions:
http://cpearson.com/excel/PrintMultipleSheets.aspx

 

[Deepak]

You both are advised to use code tags while posting macros in body.

 

[Peyton]

Thanks for the recent response. I have another option I am looking at. I have a code that works and prints to PDF. How can I get it to reference a certain cell so the file name will change every month as the cell will change every month.

I want the file name to come from tab "Cover Page" and reference cell H4.

'Sheets(Array("Sheet1", "Sheet5")).Select ' not all

Sheets.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _

  "NewBook.pdf", Quality:=xlQualityStandard, _

  IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

  True

Sheets(1).Select

End Sub

I have multiple print options in my file . What can be done to tweak the above formula again to only print certain files or not print certain files?

Thanks. You have been a big help!

 

[vletm]

I need whole Sub to try to help.
It would be much better, if You could send the whole sample file too.

 

[Peyton]

You both are advised to use code tags while posting macros in body.

I need whole Sub to try to help.
It would be much better, if You could send the whole sample file too.

That is the whole sub...

I attached a sample file and left my macros in there. The first 2 buttons work but not the way I want them to. The first one is almost there but I need to reference a cell. The other buttons I want to print certain sheets and not all of them.

Thank you again for looking at this.

 

[Peyton]

You both are advised to use code tags while posting macros in body.

Did I do it right?

 

[vletm]

I changed and tested Your 'Sub'; check 'Print_DryMix.png'.
It saves ... somewhere that file.
If You have to change that file to different folder ... gotta make minor modification. Okay?

 

[Peyton]

Will you please sent me the test file? I can't seem to make it work.

I keyed it in but it won't work...unless you key something I did wrong?

Sub Print_DryMix()
 
  File_Name = Worksheets("Cover Page").Range("H2")
'Sheets(Array("Sheet1", "Sheet5")). Select ' not all
  Sheets.Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=File_Name, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, IngorePrintAreas:=False, OpenAfterPublish:= _
  True
  Sheets(1).Select
 
End Sub

Thanks again for taking time out to help.

 

[Peyton]

I found my error... I misspelled Ignore. This is perfect.

I ran it and it runs for all tabs. What do I tweak in the formula to run certain tabs only?

 

[vletm]

How do You want to select tab or tabs?
Now, there is line 'Sheets.Select', which selects all sheets.
If You mark previous lines 1st symbol ( ' ) in the front of that line,
It will print only active tab.
If You want always select same certain tabs,
You can edit that 'not all line like "Sheet1" > "Consolidated" and so on.
Of course, You gotta take the 1st symbol ( ' ) away.
Ideas?

 

[Peyton]

Select them by clicking a command button. So it will be preset on each button what gets pulled.

1st button = All sheets
2nd button = Specified Sheets (Consolidated, Regional Sales, Regional Growth Chart)
3rd button = Specified Sheets (Northwest Regional Sales, NW Regional Growth Chart)

Something like that.

 

[Peyton]

So the code you gave me worked! I changed the Sheets.Select to show Sheets(Array("xxxxx", "etc", "and so forth"))

My question now is can this be done without using the name of actual tabs and instead use "Sheet 1", "Sheet 2", and so forth.

Can it be done?

 

[vletm]

1) selection
Think something like 'sample.png'.
You could select tabs ( those names are from different use in photo ) that You need and press [Print]. You could make many kind of combinations. Okay?

2) No need to use tab's names. Test next line:
Sheets(Array(1, 3, 5))
1 = 1st tab, 3 = 3rd tab, 5 = 5th tab and so on...

 

[Peyton]

Thank you. This worked perfect. Again thank you for taking the time to help me solve this.