Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-07-23
9
Medium Priority
?
367 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

579 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