Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Placing a Header and/or Footer in a Macro routine

Posted on 2015-01-06
14
Medium Priority
?
84 Views
Last Modified: 2015-01-07
EE Pros,

I have a Macro (part of it) that Wilder1626 did for me that has helped me print from my Worksheet. Gowflow also helped out with a selection capability.  Below is the base code.

Here is my simple question.  If I wanted to put a header or footer in this printout, what line/syntax would I add in order to do it?

Thank you in advance.


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


B.
0
Comment
Question by:Bright01
[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
  • 7
  • 7
14 Comments
 
LVL 26

Expert Comment

by:pony10us
ID: 40533647
You don't mention what you want in the header so

Custom text:  

Worksheets("SomeSheet").PageSetup.LeftHeader = "Some Text"


Workbook name:  

ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName


Contents of Cell B5:  

ActiveSheet.PageSetup.LeftHeader = _    
     Format(Worksheets("Sheet2").Range("B5").Value)

Substitute Footer for Header and Center or Right for Left to adjust.
0
 

Author Comment

by:Bright01
ID: 40533663
Pony10us,

Got it.  Only one small problem.  When I put in the "Some Text" how do I control the Font Size?  Right now it comes out very small and hard to read.

Thanks!

b.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40533686
Try:

Worksheets("SomeSheet").PageSetup.LeftHeader = "&""Courier New"&10 Some Text"
 
Should give you font: Courier New at size: 10.  Play with it until you get the font/size you want.
0
Technology Partners: 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!

 

Author Comment

by:Bright01
ID: 40533883
It's not working; here is the code....can you take a look?

Sub PrintoutUseCases()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&""10 Confidential"
End With
With Worksheets("Use_Cases").PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 3
.PaperSize = xlPaperA4
End With
Sheets("Use_Cases").Select
ActiveSheet.PageSetup.PrintArea = ("UseCaseRange")
'ActiveSheet.PageSetup.LeftFooter = ThisWorkbook.FullName
ActiveWindow.SelectedSheets.PrintPreview
Sheets("Use_Cases").Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40534008
With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&""10 Confidential"



With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&10 Confidential"
0
 

Author Comment

by:Bright01
ID: 40534058
pony10us,

Thanks for hanging with me on this.  I changed the line to the second line you recommended.  

Here is what is going on.....

1.) I'm getting a Header and a Footer (instead of just the RightFooter) on the page.
2.) I'm getting the full path of the workbook on the left footer.

It must be something simple I am missing.

B.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40534130
I have been trying to get this to work and I don't get any header/footer so apparently I am missing something as well.  The code runs error free. I will keep looking but I don't see any reason for why you are seeing additional information like the full path.

Do you have anything defined under Page Layout/Sheet Options/Header and Footer?
0
 

Author Comment

by:Bright01
ID: 40534159
OK.... I think I may have something here.  I got it to work with the RightFooter line.  But it didn't clear the previous print output (so I still have the original LeftFooter, LeftHeader and Path reference text on the output.  Is there a way to clear previous setups?  It must be storing it somewhere within the workbook but I would think there would be a reset or clear line we could use.

B.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40534186
That part's easy (a little slow though so there may be a better way)

With Sheets("Use_Cases").PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With
0
 

Author Comment

by:Bright01
ID: 40534331
Pony10us;  

That worked!  I have one last question.  I have a Customer Name on another tab that I would like to reference in the Footer  (i.e. Customer Name    Confidential).  The Tab is "Worksheet1" and the field is B6.  What do I change on the single line to include that reference point?  

With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&""10 Confidential"

I promise this is the last question on this "ask"..... Thank you very much.

B.
0
 
LVL 26

Accepted Solution

by:
pony10us earned 2000 total points
ID: 40534424
Not sure about this however I think it would be this way:

With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&10" & Worksheets("Sheet2").Range("B6").Value & "   Confidential"
0
 

Author Closing Comment

by:Bright01
ID: 40534761
Like a charm!  Works great..... just what was needed.  THANK YOU!

B.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40535786
Glad to help.  Now I need to find out why it isn't working on mine.  It makes life much easier when I can see that my code works before posting.  :)
0
 

Author Comment

by:Bright01
ID: 40535795
Post a question and I'll try to help you out !!! ;-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…

719 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