Solved

Pint Range in Excel

Posted on 2015-01-05
15
51 Views
Last Modified: 2015-01-06
EE Pros,

I'm trying to understand how I can define a range and then have it print from a Macro that identifies all the Ranges in a Workbook.  I've attached a simple file with 3 tabs with Ranges to print.  How do I define the print area or ranges and then have a Macro that prints them all?  What about page breaks?

That's it.  Thank you in advance.

B.
Print-Area.xls
0
Comment
Question by:Bright01
  • 6
  • 5
  • 4
15 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40532669
Lets be a bit more specific here.

You want what ?
gowflow
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40532676
You can try. That would be an easy and simple way.

Unless you have more specification.

Worksheets("Sheet1").Range("D8:M22").printout

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 40532682
By being specific obviously was a bit more in depth of a simple printout property

I am referring to your comment

a Macro that identifies all the Ranges in a Workbook.

and knowing how detailed you are and how precise this sentence left me speechless as to how do you envision a macro to just simply 'identifies all ranges in a workbook' and the workbook you posted has no named ranges and nothing that indicate that something is a range except the yellow colored area that are not enough to indicate a range.

So this respect pls clarify your request.
gowflow
0
 

Author Comment

by:Bright01
ID: 40532790
Gowflow and Wilder1626,

I'm sorry for not being more specific.  What I need is a "print" macro that allows me, when the macro is run from a "PRINT BUTTON", to run a set of range names and then have it properly print the ranges in either a PowerPoint or a word format.  The yellow areas are only there to signify a area to print.  I was going to figure out how to expand the printing area once I saw how it's done.

Hope that helps with clarity.

Thank you,

B.
0
 

Author Comment

by:Bright01
ID: 40533308
Wilder and Gowflow,

Quick update.  I took Wilder's single line code and it worked (and keeps things simple).  My only question to you now is if I'm using range names, what is the syntax for printing multiple range names when running the macro?  Thanks for the fast and simple solution.

B.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40533323
Do you want to print every sheets on the same page?

an example could be:
Sheets("Sheet1").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$47"

Sheets("Sheet2").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$94"

Sheets(Array("Sheet1", "Sheet2")).Select

ActiveWindow.SelectedSheets.PrintPreview
Sheets("Sheet1").Select

Open in new window


Then, you call the printout
0
 

Author Comment

by:Bright01
ID: 40533329
I was thinking more like;

Worksheets("Sheet1").Range("Rangename1, Rangename2, Rangename3").printout

Would that work?

B.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Expert Comment

by:Wilder1626
ID: 40533332
Bright01 , i think he wants to print multiple ranges from multiple sheets.
0
 

Author Comment

by:Bright01
ID: 40533335
I would suspect that if that is the case then;

Sub Print Worksheets

Worksheets("Sheet1").Range("Rangename1, Rangename2, Rangename3").printout
Worksheets("Sheet2").Range("rangename4, rangename5.printout

End Sub

right?
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40533360
in the above macro, you run the printout twice. right?

Like this code bellow, you will get a print preview and print only once.

Sheets("Sheet1").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$47"

Sheets("Sheet2").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$94"

Sheets(Array("Sheet1", "Sheet2")).Select

ActiveWindow.SelectedSheets.PrintPreview
Sheets("Sheet1").Select

Open in new window

0
 

Author Comment

by:Bright01
ID: 40533364
Love it!  One last question; how do I control landscape vs. portrait?  I have one Worksheet needs to be portrait and one that needs to be landscaped.....

Much thanks!!!!!

B.
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 300 total points
ID: 40533374
This would do it.

With Worksheets("Sheet1").PageSetup
         .Orientation = xlLandscape
         .Zoom = False
         .FitToPagesWide = 1
         .FitToPagesTall = 1
         .PaperSize = xlPaperA4
    End With

With Worksheets("Sheet2").PageSetup
         .Orientation = xlPortrait
         .Zoom = False
         .FitToPagesWide = 1
         .FitToPagesTall = 1
         .PaperSize = xlPaperA4
    End With
    
    
    Sheets("Sheet1").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$47"

Sheets("Sheet2").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$94"

Sheets(Array("Sheet1", "Sheet2")).Select

ActiveWindow.SelectedSheets.PrintPreview
Sheets("Sheet1").Select

Open in new window


If this work for you, please make sure to accept the solution at the end.

Thanks
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 200 total points
ID: 40533421
try this file.
gowflow
Print-Area-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40533424
only problem is when ranges overspill working on it but hv to go now
gowflow
0
 

Author Closing Comment

by:Bright01
ID: 40533590
Much Thanks to both of you.  I modified Wilder's code to work in my WB but after getting gowflow's code, realized that there may be another approach through the drop down selection capability that he has demonstrated for me.  You guys are awesome and I really appreciate your work (although my original commentary left a lot of room for interpretation!).

Again, very much appreciate your time.  Will be authoring another (simpler) question shortly.

Best regards,


Bright 01
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 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

24 Experts available now in Live!

Get 1:1 Help Now