Solved

Placing a Header and/or Footer in a Macro routine

Posted on 2015-01-06
14
81 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
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!

 

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 500 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

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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 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