General discussion

Locked

Excel Printing

By moores ·
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:aj12 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.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Printing

by DKlippert In reply to Excel Printing

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.

Collapse -

Excel Printing

by moores In reply to Excel Printing

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.

Collapse -

Excel Printing

by moores In reply to Excel Printing

Point value changed by question poster.

Collapse -

Excel Printing

by a.barnes In reply to Excel Printing

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

Collapse -

Excel Printing

by moores In reply to Excel Printing

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

Collapse -

Excel Printing

by moores In reply to Excel Printing

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums