From Calc Guide (PDF file): https://wiki.documentfoundation.org/images/4/47/CG41-CalcGuideLO.pdf
Headers and footers are predefined pieces of text that are printed at the top or bottom of a sheet outside of the sheet area. Headers are set the same way as footers. You can choose to have different content on left and right pages of the same page style. Headers and footers are assigned to a page style. You can define more than one page style for a spreadsheet and assign different page styles to different sheets.
Menu Format | Styles and Formatting | Page Styles tab - define style (right click on empty space and New or click on existing page style and click on Modify). When done double click newly created page style to apply style to current sheet. Click on second sheet (bottom tab) and repeat the task (create new style and double click on it to apply it to current sheet).