Software

Ask something
Discussion Locked

Excel Printing

0 helpful
6 replies

by moores

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to automate the following printing procedure:

I have a workbook that calculates the values of our clients' investment portfolios. The first sheet (named 'data') holds all the variables and does not need to be printed out. The rest of the sheets are named for the client whose portfolio is being valued and my users update the workbook quarterly with new clients, so the number of worksheets changes. On each worksheet there are two ranges which I need to be printed: (b31:j90) which is best printed portrait, and (s91:aj128) best printed landscape.

To try to make things easier I have inserted a blank worksheet named 'First' after 'data' and one named 'last' as the last sheet in the workbook. I am trying to get a macro to selectall sheets from 'first' to 'last' and then print out the above ranges, changing the page orientation between printouts.

6 replies

by dklippert

As a suggestion,
Set up the Worksheet the way you want using File>Print Area and Page set up.
Go to View>Custom Views name and add that view. Do the same for any other pages you want to print.
Now go to View>Report Manager. Add the Views to the list and print.

by moores

Thanks for the suggestion DKlippert but I need a solution to AUTOMATE the printing of over 80 worksheets from this workbook. The Views option involves too much manual input.

by abarnes70

Create a new macro called mcrAutoPrint and add this code to it. You can add extra functionality by adding lines to the 'With' blocks, such as printing a different header/footer for each of the print areas, different zoom values etc.

Sub mcrAutoPrint()
Dim strPrintArea1 As String
Dim strPrintArea2 As String

strPrintArea1 = "B31:J90"
strPrintArea2 = "S91:AJ128"

For Each x In ActiveWorkbook.Sheets
x.Select
If ActiveSheet.Name = "data" Then
Else
With ActiveSheet.PageSetup
.PrintArea = strPrintArea1
.Orientation = xlPortrait
End With
ActiveSheet.PrintOut
With ActiveSheet.PageSetup .PrintArea = strPrintArea2
.Orientation = xlLandscape
End With
ActiveSheet.PrintOut
End If
Next x
End Sub

by moores

Thanks, fair solution, the data sheet prints out but otherwise it works fine.

This discussion is closed to new replies.