Omit header from first page without embedding header in code

[ibvalentine]

I want to print a multi-page worksheet omitting the header on the first page.
However, I also want to create a macro to do this without embedding the
header info in the code. I do not know VBA programming, but I have been
reading some web sites and playing around with it.

Here's a solution that I thought would work (maybe it just needs some
tweaking):

Sub NoFirstPageHeader_AllSheets()
Dim wsSheet As Worksheet
Dim sHeader As String
For Each wsSheet In Worksheets
With wsSheet
sHeader = .PageSetup.LeftHeader
.PageSetup.LeftHeader = ""
sHeader = .PageSetup.CenterHeader
.PageSetup.CenterHeader = ""
sHeader = .PageSetup.RightHeader
.PageSetup.RightHeader = ""
.PrintOut From:=1, To:=1
sHeader = .PageSetup.LeftHeader
sHeader = .CenterHeader
sHeader = .RightHeader
.PrintOut From:=2
End With
Next wsSheet
End Sub

The problem is that this code deletes my header entirely instead of what I
intended it to do (omit the header on the first page). I tried to replace
"PageSetup" with "Print" but that, of course, did not work. I am surprised
that doing a simple thing like omitting the header on the first page is such
a big deal.

I have come up with a few workarounds, but if anyone knows of a macro that
can do what I described, please let me know. Again, the sticky part is that I
want this to work in any workbook with different page headers.

Thanks!

 

[OssieMac]

Hi ibvalentine,

The reasons it does not work is because you are saving the null ("") into
the headers again instead putting the saved value back into the headers and
also you cannot use the same variable sHeader to save 3 different variables.
You need one each.

Try this and see how it goes.

Sub NoFirstPageHeader_AllSheets()
Dim wsSheet As Worksheet
Dim strLeft As String
Dim strRight As String
Dim strCenter As String

For Each wsSheet In Worksheets
With wsSheet
strLeft = .PageSetup.leftHeader
.PageSetup.leftHeader = ""
strCenter = .PageSetup.CenterHeader
.PageSetup.CenterHeader = ""
strRight = .PageSetup.rightHeader
.PageSetup.rightHeader = ""
.PrintOut From:=1, To:=1
.PageSetup.leftHeader = strLeft
.PageSetup.CenterHeader = strCenter
.PageSetup.rightHeader = strRight
.PrintOut From:=2
End With
Next wsSheet
End Sub

Regards,

OssieMac

 

[OssieMac]

Afterthought and a couple of tips:

You will need to go through page setup and set the headers before you begin.
if you want the macro to do the headers then the easy way is to record a
macro while inserting them and then insert it where required.

Tip 1:
Your code loops through each worksheet indicating that you want to print
multiple worksheets. Therefore if the headers are the same on all worksheets
then you can save yourself some time by selecting all the worksheets. (Hold
the Ctrl and click on each individual tab) and you only have to set the
headers once and it will be applied to all of the selected worksheets.

Tip 2:
You can also select all the worksheets as one print job and it will print
all the worksheets and if you have page numbering, it will number them all in
sequence. You can run page setup all together as per Tip 1 or you can run
page setup individually with different setup for each worksheet and still
select them all for the print job. However, if printing from a macro, instead
of using
For Each wsSheet In Worksheets, you would need to select all the required
worksheets. You can record a macro to get the code for this.



Regards,

OssieMac

 

[ibvalentine]

OssieMac

Your solution worked beautifully! Thank you! I have one follow-up question
if you can spare another minute or two. (I also think your answer will help
others that will find your solution in a search.)

My problem was with just one long worksheet that would result in several
pages when printed. However, you provided two good tips when applying this
solution to other worksheets.

In Tip 1, you say the code will loop through each worksheet. Does that mean
that the header will be omitted on the first page of each worksheet?

Scenario 1: 5 worksheets, all multi-page, all grouped, header in first
worksheet only. When printed will the header print on all worksheets and will
just the first page of the first worksheet be omitted or will the header on
the first page of each worksheet be omitted?

Scenario 2: Same as scenario 1, but this time each worksheet has a header of
its own. Same question as scenario 1.

Thanks again!

John

 

[OssieMac]

See answers inserted below each of your questions.

I'll insert a separate reply with 4 different macros. The comments at the
top of each explains their function. You will find that all your Scenarios
can be accommodated.

If you find all this helpful then if you are logging in on the MS
Communities website then don't forget to answer 'Yes' to the question re was
this post helpful.

Regards,

OssieMac

OssieMac

Your solution worked beautifully! Thank you! I have one follow-up question
if you can spare another minute or two. (I also think your answer will help
others that will find your solution in a search.)

My problem was with just one long worksheet that would result in several
pages when printed. However, you provided two good tips when applying this
solution to other worksheets.

In Tip 1, you say the code will loop through each worksheet. Does that mean
that the header will be omitted on the first page of each worksheet?

OssieMac's Answer: Yes

Scenario 1: 5 worksheets, all multi-page, all grouped, header in first
worksheet only. When printed will the header print on all worksheets and will
just the first page of the first worksheet be omitted or will the header on
the first page of each worksheet be omitted?

Answer: header on the first page of each worksheet be omitted?

Scenario 2: Same as scenario 1, but this time each worksheet has a header of
its own. Same question as scenario 1.

OssieMac's Answer: header on the first page of each worksheet be omitted?

 

[OssieMac]

Four different macros with slightly different functions. See the comments at
the top of each.

Regards,

OssieMac

Sub One_worksheet_Multi_Page()
'Printing from one worksheet only with multiple pages
'No header on first printed sheet.

Dim wsSheet As Worksheet
Dim strLeft As String
Dim strRight As String
Dim strCenter As String

Set wsSheet = Sheets("Sheet1")
With wsSheet
strLeft = .PageSetup.leftHeader
.PageSetup.leftHeader = ""
strCenter = .PageSetup.CenterHeader
.PageSetup.CenterHeader = ""
strRight = .PageSetup.rightHeader
.PageSetup.rightHeader = ""
.PrintOut From:=1, To:=1
.PageSetup.leftHeader = strLeft
.PageSetup.CenterHeader = strCenter
.PageSetup.rightHeader = strRight
.PrintOut From:=2
End With
End Sub

Sub Multi_WS_Looping_1()
'Printing multiple worksheets looping through
'the individual sheets.
'No header on first printed page of each worksheet.
'Each worksheet page numbering starts at 1.

Dim wsSheet As Worksheet
Dim strLeft As String
Dim strRight As String
Dim strCenter As String

For Each wsSheet In Worksheets
With wsSheet
strLeft = .PageSetup.leftHeader
.PageSetup.leftHeader = ""
strCenter = .PageSetup.CenterHeader
.PageSetup.CenterHeader = ""
strRight = .PageSetup.rightHeader
.PageSetup.rightHeader = ""
.PrintOut From:=1, To:=1
.PageSetup.leftHeader = strLeft
.PageSetup.CenterHeader = strCenter
.PageSetup.rightHeader = strRight
.PrintOut From:=2
End With
Next wsSheet
End Sub

Sub Multi_WS_Looping_2()
'Printing from multiple worksheets looping through
'the individual sheets.
'No header on first printed page of first worksheet.
'Remaining worksheets have headers on all pages.
'Each worksheet page numbering starts at 1.

Dim wsSheet As Worksheet
Dim strLeft As String
Dim strRight As String
Dim strCenter As String
Dim firstSht As Boolean

firstSht = False
For Each wsSheet In Worksheets
With wsSheet
'Save page headers
strLeft = .PageSetup.leftHeader
strCenter = .PageSetup.CenterHeader
strRight = .PageSetup.rightHeader
If firstSht = False Then 'No shts printed yet
'Remove page headers
.PageSetup.leftHeader = ""
.PageSetup.CenterHeader = ""
.PageSetup.rightHeader = ""
firstSht = True
End If
.PrintOut From:=1, To:=1
'Replace page headers
.PageSetup.leftHeader = strLeft
.PageSetup.CenterHeader = strCenter
.PageSetup.rightHeader = strRight
.PrintOut From:=2
End With
Next wsSheet
End Sub

Sub Multi_WS_Select()
'Printing multiple worksheets using multiple
'worksheet select method.
'No header on first printed page.
'This method allows for continuous.
'page numbering for all selected worksheets.

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate

With ActiveSheet.PageSetup
'Save page headers
strLeft = .leftHeader
strCenter = .CenterHeader
strRight = .rightHeader
.leftHeader = ""
.CenterHeader = ""
.rightHeader = ""
Sheets(Array("Sheet1", "Sheet2", "Sheet3")) _
.PrintOut From:=1, To:=1
'Replace page headers
.leftHeader = strLeft
.CenterHeader = strCenter
.rightHeader = strRight
Sheets(Array("Sheet1", "Sheet2", "Sheet3")) _
.PrintOut From:=2
End With
End Sub

 

[ibvalentine]

OssieMac,

Once again, thank you for the thoroughness of your solution and your
generosity in sharing it. I searched for the solution before I posted my
original message and I noticed that this question is asked a lot in various
ways. I don't know VBA but I know other languages and your code makes sense
when I study it. Very helpful! Your solution is the most definitive and I am
sure others also will benefit from your responses.

John