VBA printing challenges

[mrpwagner]

I am new to VBA. I have been researching on the internet and can't find answers to my questions. I look for ward to assistance.

I need to print two worksheets and two ranges (fixed - not dynamic) from two OTHER worksheets.

I have gotten parts of it to work but I am not able to combine them.

PRINTING THE SHEETS:

Private Sub CommandButton1_Click()


Sheets(Array("2016.08 Printout", "2016.08 Calculations")).Select




Sheets(Array("2016.08 Printout", "2016.08 Calculations")).PrintOut





End Sub

THE ABOVE WORKS

PRINTING ONE OF THE RANGES

Sub Macro2()
'
' Macro2 Macro
'


'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Assignments").Select
Range("C744:O758").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub



THIS WORKS INDEPENDENTLY AS WELL

Copied the above to another macro and changed the parameters:

Sub Macro3()
'
' Macro3 Macro
'


'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Unavailability").Select
Range("C794:BB820").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub





THIS DOES NOT PRINT THE SELECTED RANGE!


Ideally the macro would print the two worksheets (2016 Printout and 2016 Calculations and the specific ranges from the assignments and unavailability worksheets.


Please speak to me as if I know nothing about VBA - as that would be the case.



I appreciate your help in advance.

Thank you!

 

[dchaney]

You should be able to do the following, this will set the print area and orientation for the two sheets then print all 4

Sub test()

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Sheets("Assignments").Select
With ActiveSheet.PageSetup
    .PrintArea = "$C$744:$O$758"
    .Orientation = xlLandscape
End With

Sheets("Unavailability").Select
With ActiveSheet.PageSetup
    .PrintArea = "$C$794:$BB$820"
    .Orientation = xlLandscape
End With

Sheets(Array("2016.08 Printout", "2016.08 Calculations", "Assignments", "Unavailability")).PrintOut

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

 

[mrpwagner]

Thank you so much!!!!.


I copied and pasted the post and I got it to run fairly well.

I print this to a PDF. It give me the option to save three times. I have saved it under a an A,B,C nomenclature.

The A file never saves.

I have all the worksheets I want, But spread over two files.

Any suggestions?


Thanks.

MW

 

[dchaney]

I am sorry I missed you asking for additional help... Paste the following in the VBA module named "Thisworkbook" this will print all 4 pages to PDF files and save them to your default save location (My Documents normally) then open that Directory so you can see your files. I am sure there are other ways to do this but I am still learning so I hope this helps.

Sub test()

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Sheets("Assignments").Select
With ActiveSheet.PageSetup
    .PrintArea = "$C$744:$O$758"
    .Orientation = xlLandscape
End With

Sheets("Unavailability").Select
With ActiveSheet.PageSetup
    .PrintArea = "$C$794:$BB$820"
    .Orientation = xlLandscape
End With

Sheets("2016.08 Printout").ExportAsFixedFormat Type:=xlTypePDF, Filename:="2016.08 Printout.pdf", OpenAfterPublish:=False
Sheets("2016.08 Calculations").ExportAsFixedFormat Type:=xlTypePDF, Filename:="2016.08 Calculations.pdf", OpenAfterPublish:=False
Sheets("Assignments").ExportAsFixedFormat Type:=xlTypePDF, Filename:="Assignments.pdf", OpenAfterPublish:=False
Sheets("Unavailability").ExportAsFixedFormat Type:=xlTypePDF, Filename:="Unavailability.pdf", OpenAfterPublish:=False

Shell "explorer.exe" & " " & "C:\Users\[B][COLOR=#ff0000]PUT YOUR USER NAME HERE[/COLOR][/B]\Documents", vbNormalFocus

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Make sure you change the red text above as indicated

 

[dchaney]

Are you wanting these all to save as ONE PDF file?

 

[dchaney]

MrPWagner,

I have played with this code a bit more and I hope this is exactly what you're looking for... This file sets all of the print areas as before and then saves 1 PDF file (All pages) to your desktop AND opens the file after it saves it.

Just paste this code into the "ThisWorkbook" Module (click here for instructions if needed) within the VBA editor and change the RED text within the code to what ever your computer logon name is (i.e. mrpwagner).

Sub PrintToPDF()

Dim wbCurrent As String

Application.ScreenUpdating = False

wbCurrent = ActiveWorkbook.Name

With Sheets("Assignments").PageSetup
    .PrintArea = "$C$744:$O$758"
    .Orientation = xlLandscape
End With

With Sheets("Unavailability").PageSetup
    .PrintArea = "$C$794:$BB$820"
    .Orientation = xlLandscape
End With

ThisWorkbook.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Users\dchaney\Desktop\" & wbCurrent & ".pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

Application.ScreenUpdating = True

End Sub

 

[mrpwagner]

Thank you for your diligent efforts! I will try this later today. I assume I can change the file location to anywhere I want by the usual nomenclature.

I'll let you know as soon as I try how it works.

 

[mrpwagner]

I used the below macro you sent me an it WORKED PERFECTLY.......

When I run this the first sheet that prints is the unavailability. The other three print in the correct order. I would like this to print last.

I did move the unavailability to the last worksheet; this didn't work.

TIPS?

Thank you.

Sub Macro4()

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With


Sheets("Assignments").Select
With ActiveSheet.PageSetup
.Printarea = "$C$744:$O$758"
.Orientation = xlLandscape
End With


Sheets("Unavailability").Select
With ActiveSheet.PageSetup
.Printarea = "$C$700:$BB$725"
.Orientation = xlLandscape
End With


Sheets(Array("2016.08 Printout", "2016.08 Calculations", "Assignments", "Unavailability")).PrintOut


With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With


End Sub

 

[dchaney]

Add the following just above the Sheet Array

Sheets("[COLOR=#333333]2016.08 Printout"[/COLOR]).Activate

This will activate the "2016.08 Printout" sheet in your workbook and print it first, then it should print them one after another in the order they are placed within the Excel file.

 

[mrpwagner]

Thank you!!! It is working perfectly now.

And I have created other macros by copying this one and just adjusting it for the month. I also created a control button so one click and everything prints!!!!!