Placing a Header and/or Footer in a Macro routine

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.
Bright01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pony10usConnect With a Mentor Commented:
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
 
pony10usCommented:
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
 
Bright01Author Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pony10usCommented:
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
 
Bright01Author Commented:
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
 
pony10usCommented:
With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&""10 Confidential"



With Worksheets("Use_Cases").PageSetup.RightFooter = "&""Courier New""&10 Confidential"
0
 
Bright01Author Commented:
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
 
pony10usCommented:
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
 
Bright01Author Commented:
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
 
pony10usCommented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
Like a charm!  Works great..... just what was needed.  THANK YOU!

B.
0
 
pony10usCommented:
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
 
Bright01Author Commented:
Post a question and I'll try to help you out !!! ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.