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?

[Webinar] Streamline your web hosting managementRegister Today

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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
All Courses

From novice to tech pro — start learning today.