Solved

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

Posted on 2014-07-23
9
365 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

695 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