Wrong header on odd and even pages when printing workbook

Jxe

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a workbook containing 8 sheets, one first page and seven for each day of the week.

Every weekday sheet has two pages, one with the header ”Morning” and one with the header ”Afternoon”, using different header on odd and even pages.

The first sheet contians only one pages without any header.

When trying to print the full workbook the first page without header counts against odd and even pages, so that the the Morning and Afternoon header gets on the wrong page. How can I avoid this?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jxe

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Add another dummy empty page? :)
Does not solve the problem. I’m printing on both pages of the paper to get correct page spreads two and two. Forgot to add that to the question.

If its not possible to add an extra dummy page and hide it when printing?
 
Upvote 0

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
458
Office Version
  1. 365
Platform
  1. Windows
Would setting a first page number help? Just throwing ideas here really.
 
Upvote 0

Jxe

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Would setting a first page number help? Just throwing ideas here really.
Tried that without success. Keep throwing ideas if there’s more :)
 
Upvote 0

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
458
Office Version
  1. 365
Platform
  1. Windows
Does it work as intended if you hide the 1st sheet? If so, maybe a little macro to hide, print and unhide would solve your problem.
 
Upvote 0

Jxe

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Does it work as intended if you hide the 1st sheet? If so, maybe a little macro to hide, print and unhide would solve your problem.
Yes, if i hide the first sheet the rest prints as intended. I have no knowledge about macros in Excel. How would one like the one you suggest look like?
 
Upvote 0

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
458
Office Version
  1. 365
Platform
  1. Windows
I will paste a code, but I am not sure if it would be what you want or not. I don't have a printer to test with and print to pdf involves saving a file so not the same thing?
You may start a new thread about it perhaps with the below code as a starter.

Make a copy of your original file
Save workbook as xlsm (macro enabled workbook)
Open VBA Editor (ALT + F11)
DoubleClick ThisWorkbook on the Project pane (will open a new window)
Select "Workbook" from combobox on top left of new window, and "BeforePrint" from the combobox on right of that.
Paste the code (excluding the first and last lines, they will be auto-populated when you select in the above step) and change sheet_name_here with the sheet's name you want to hide.


VBA Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

    With ThisWorkBook
        .Worksheets("sheet_name_here").Visible = xlSheetHidden
        Application.EnableEvents = False
        .PrintOut 'Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Application.EnableEvents = True
        .Worksheets("sheet_name_here").Visible = xlSheetVisible
    End With
    
    Cancel = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,186,770
Messages
5,959,657
Members
438,440
Latest member
Hi577

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top