Excel worksheet formatting for multiple sheets in workbook?

[mishmish3000]

Good morning and happy pre-Caturday to all!
Is there a relatively simple way to copy the header and footer information, margins and other printing formatting from one worksheet to another? I have 13 worksheets in a workbook. I've formatted the first one with header, footer, margins, and printing format as well as page orientation and was wondering if there's a lovely way to just tell all the other sheets to do the same thing.
Header: Left side, name of region (a field in the sheet); middle, name of report (always Late Start DTaP); right, date.
Footer: Page N of X in center
Orientation is landscape and the page is set to print at 83% of normal so all columns fit on one page.
Gridlines should show for all records.
The top row, with column headers, should show on every page.
I'll attach a sample for your experimental enjoyment. LOL
MishMish3000

[PJ_in_FL]

Have a look at this routine that copies the header, including formatting, from Sheet1 to Sheet2. Add the additional properties from the PageSetup collection of the sheet as you need to duplicate all the relevant (to you) formatting properties.

Code: Select all

Sub gethead()
Dim pgs As PageSetup

    Set pgs = ActiveWorkbook.Sheets("Sheet1").PageSetup

    ActiveWorkbook.Sheets("Sheet2").PageSetup.LeftHeader = pgs.LeftHeader
    ActiveWorkbook.Sheets("Sheet2").PageSetup.CenterHeader = pgs.CenterHeader
    ActiveWorkbook.Sheets("Sheet2").PageSetup.RightHeader = pgs.RightHeader
    

End Sub

HTH

[mishmish3000]

I'll certainly try it out! Any help at all is appreciated, since I don't want to have to format 13 sheets in 2 workbooks every month (we're working on producing reports from the central office of the Dept. of Health, for the 13 health regions in TN to use. They can use them to track kids who are missing an important vaccination--DTaP. If we can get the kids their 3rd and 4th DTaP shots (Diptheria, Tetanus and Pertussis vaccination), they'll do much better healthwise.

Most kids get all 4 shots in the series, but sometimes parents forget to bring them back for all of their vaccinations. Pertussis, especially, is very bad for children to get. Oh, and if you're a grandparent or great grandparent (of course, perhaps most of us aren't THAT old), it's a very good idea to get another booster shot of DTaP before hanging out with new babies. That way, you won't inadvertently give pertussis to your grandkid. Pertussis is also known as whooping cough.
MishMish3000

[Jan Karel Pieterse]

If you're setting up the page setup manually for one sheet, just select all sheets first (click the first, shift+click the last) and THEN setup the page headers and such.
Don't forget to unselect the sheets (right-click, ungroup)

[mishmish3000]

Excellent idea!!! Thanks!!
MishMish3000