?
Solved

Print Range Macro

Posted on 2012-08-10
5
Medium Priority
?
417 Views
Last Modified: 2012-08-13
Hi,
I would like to use a macro to cycle through multiple tabs in a workbook and setup the print range.  I want  each tab to be a page and all contents to fit on one page in landscape on a standard 8.5 by 11 page.  

Thanks
Montrof
0
Comment
Question by:montrof
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 38282055
Try this code:

Sub MyMacro
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        
        On Error Resume Next
    Next ws
    
End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
ID: 38282075
Try this on your selected sheets

Sub Macro1()
'
' Macro1 Macro
'
Dim sht As Worksheet

Application.PrintCommunication = False

For Each sht In ActiveWindow.SelectedSheets
    
    With sht.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    
Next sht

Application.PrintCommunication = True
End Sub

Open in new window


Thomas
0
 
LVL 1

Author Comment

by:montrof
ID: 38282108
Thank you for the Macros but neither work, Each tab is still showing mutiple pages. I do not know if it maters but there are graphs on the tabs.
0
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 2000 total points
ID: 38282253
Please try this modification:

    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        
        On Error Resume Next
    Next ws

Open in new window

0
 
LVL 1

Author Closing Comment

by:montrof
ID: 38287344
Perfect thank you so much for the help
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question