?
Solved

How do I setup print area for the selected sheets in excel 2002?

Posted on 2014-07-23
9
Medium Priority
?
366 Views
Last Modified: 2014-07-26
I have 15 sheets in a workbook and want to select 11 of them.  I want to automatically set print area on the selected sheets based on the content,  scale it so that all columns appear on 1 page and print it...

I have a macro for the sheets I want to print as below...


' Print Selected Schedule Sheets
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
    Sheets(Array("527-1", "527-2", "527-3", "527-5", "627-2", "627-3", "627-4", "627-5", _
        "241 #1", "241 #2", "MO-3")).Select
    Sheets("527-1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub
0
Comment
Question by:eashan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40215087
Try this code:
 
Sub Print_Sheets()
    Dim shts As Sheets
    Dim sh As Object
    Set shts = Sheets(Array("527-1", "527-2", "527-3", "527-5", "627-2", "627-3", "627-4", "627-5", _
         "241 #1", "241 #2", "MO-3"))
    For Each sh In shts
        With sh.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
        End With
    Next sh
    shts.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:eashan
ID: 40215610
Works...but a small issue... I would like to select the contents as follows..

Columns a through H
rows.. select all rows before the first blank cell in column a after the 6th row... see attached and highlighted area...


Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40215651
Nope...no attachment. :-(
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40215673
Gonna take a shot anyway.

If column A is contiguous (no blanks throughout), then the following code should work:
 
Sub Print_Sheets()
    Dim shts As Sheets
    Dim sh As Object
    Dim intLR As Integer
    
    Set shts = Sheets(Array("527-1", "527-2", "527-3", "527-5", _
        "627-2", "627-3", "627-4", "627-5", _
        "241 #1", "241 #2", "MO-3"))
    For Each sh In shts
        intLR = sh.Range("A7").End(xlDown).Row
        With sh.PageSetup
            .PrintArea = "$A$1:$H$" & intLR
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
    Next sh
    shts.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:eashan
ID: 40215899
sorry
test.xls
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40215931
I assume you only want the area in yellow printed.  You should change "A7" to "A6" in line 10 of my code to be more accurate (in case only two rows),
0
 

Author Comment

by:eashan
ID: 40216706
rows 3 and 5 will always be blank...  Rows 1 and 2 are merged cells... row 4 has the subtitles.. I want to select all rows before row 6 plus everything until we hit the first blank row after row 6.. Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40217307
eashan,
Thanks for clearing that up.  The latest code I supplied works for your sample data layout and description.

-Glenn
0
 

Author Comment

by:eashan
ID: 40217335
Thanks
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 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