Solved

Placing a Header and/or Footer in a Macro routine

Posted on 2015-01-06
14
76 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

706 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

20 Experts available now in Live!

Get 1:1 Help Now