Solved

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

Posted on 2014-07-23
9
354 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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now