Suppress First Page Header

[Guest]

I'm working in Excel 2002.

I would like to have a header appear on the second page and the pages that
follow.
The text that I want printed in the header beginning on page 2 is Unit
Intake Report (continued).

I have tried using the following coding with no success:

Sub Test()
Dim TotPages As Long
TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
.LeftHeader = "Unit Intake Report (continued)"
ActiveSheet.PrintOut From:=2, To:=TotPages
End With
End Sub

My document is protected. I've used the above coding sandwiched between:
ActiveSheet.Unprotect
ActiveSheet.Protect

This still does not produce a header. Can I suppress the first page header
within a protected document?

Thanks

 

[Jim Cone]

Jamie,

I just ran your exact code on a protected sheet with two pages.
Ran and printed without a problem - with header printed on
the second page only. (XL2002)

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Hi Jim to print the document I was using the print button. One of my
coworkers pointed out that I needed to print from within the macro itself
clicking on the Run Sub/User Form. Doing that the document prints out great.
Thanks for your help, I appreciate it.

 

[Jim Cone]

Jamie,
You are welcome.
A way to start the macro from the worksheet is to
go to the menu bar...

Tools | Macro | Macros...

In the displayed list of macros,
select your macro and click the "Run" button.

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Hi Jim, thanks for your help. I have another question. Can I add coding
to the macro so the user can either use the print button or File, Print.
I'd like to keep them out of Tools, Macro, Macros. Also I'd like to stay
away from creating a print button on the worksheet.

 

[Jim Cone]

Jamie,

You can use the "BeforePrint" event that Excel provides.
If you do this every sheet in the workbook will use your
code when it is printed...
Jim Cone
San Francisco, USA


In the ThisWorkbook module add the following code:
'------------------------------
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Prevent normal printing.
Cancel = True
'Call the macro with printing code.
PrintTest
End Sub
'------------------------------
'------------------------------

In a standard module add the following code...
(your code with some additional lines)
'-------------------------------------
Option Explicit

Sub PrintTest()
On Error GoTo PrintError ' New line
Application.EnableEvents = False ' New line
Dim TotPages As Long
TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
.LeftHeader = "Unit Intake Report (continued)"
ActiveSheet.PrintOut From:=2, To:=TotPages
End With
PrintError: ' New line
Application.EnableEvents = True ' New line
End Sub
'------------------------------------
'------------------------------------

Hi Jim, thanks for your help. I have another question. Can I add coding
to the macro so the user can either use the print button or File, Print.
I'd like to keep them out of Tools, Macro, Macros. Also I'd like to stay
away from creating a print button on the worksheet.
Jamie

-snip-

 

[Guest]

Hi Jim, me again. Thanks so much for your time I really appreciate it. I
inserted the coding you provided. I'm getting an error message in This
Workbook. The error is: Compile error: Sub or Function not defined.

The code that is highlighted is PrintTest. I've checked the coding and it
matches yours.

I wasn't sure if I was to include Option Explicit so I entered it, but with
or without it I still receive the message. Do I need to include Option
Explicit?

Thanks so much!

 

[Jim Cone]

Jamie,

The macro in the general module should be called "PrintTest",
not the old name of "Test" ???

Jim Cone

 

[Jim Cone]

Jamie,

Further,

The PrintTest code should "not" be in the sheet module.
If it is in the sheet module you will get that error.
In the VBE, you can go to the menu bar and click
"Insert | Module" to get a standard module added to the project.

And while Option Explicit is not required, it is good practice to
include it. It will notify you of typos and force you to declare
all variables..."Dim lngNumber as Long" etc.

Regards,
Jim Cone

 

[Guest]

Jim, thank you , thank you, thank you. It is working. I appreciate you
being so patient with me and continuing to answer my questions.

Many thanks!

 

[Guest]

Oh Jim I spoke to soon. My workbook contains 12 sheets, one for each month.
For the January sheet I added info so I could get a second page to print,
however, for February's sheet I left it as is ( 1 sheet long), but the coding
forces a second page to print. The first page does not have a header, second
page does.

I'm thinking I need to have the coding check to see if there is a page break
within the sheet, if so activate the LeftHeader coding, if not ignore the
LeftHeader coding.

Does that make sense?

 

[Jim Cone]

Jamie,

Ok, some sheets have one page and others have 2 or more.
This code mod always leaves page 1 with an empty header.
If there is only one page to print, that is all you get.
Also, check out the supplemental sub at the bottom.
Regards,
Jim Cone
San Francisco, USA

"-----------------------------------
Sub PrintTest()
On Error GoTo PrintError
Application.EnableEvents = False
Dim TotPages As Long

TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
If TotPages > 1 Then
With ActiveSheet.PageSetup
.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
.LeftHeader = "Unit Intake Report (continued)"
ActiveSheet.PrintOut From:=2, To:=TotPages
End With
Else
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut
End If
PrintError:
Application.EnableEvents = True
End Sub
'-----------------------

'If, for some reason, the code gets interrupted
'and doesn't run to the end, then run this...

Sub ReturnToNormal()
Application.EnableEvents = True
End Sub

 

[Guest]

Excellent Jim!! Thank you so much it is working beautifully.