Solved

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

Posted on 2014-07-23
9
362 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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
 

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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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