Well you can start with your auto_open macro. That will automatically run when your that excel file is open. You could create make an input box pop up, input the number of sheets and what you need to add to the Header. Here is an example.
VB Code:
  1. Public Sub auto_open()

  2.  
  3. Sheets("Sheet1").Activate

  4. Application.ScreenUpdating = False

  5. ctr = 1

  6. Number = InputBox$("How many sheets do you  need?")

  7. Entry = InputBox$("Put header title here")

  8. Number = Number - 1

  9. While ctr <= Number

  10.     With ActiveSheet.PageSetup

  11.         .CenterHeader = "&P of &N"

  12.         .LeftHeader = Entry

  13.         .RightHeader = "&D" & Chr(10) & "&T"

  14.     End With

  15.     ActiveSheet.DisplayAutomaticPageBreaks = False

  16.     Sheets.Add

  17.     ctr = ctr + 1

  18. Wend

  19.  
  20. End Sub
It's not super fast put it adds the sheets and places header info.
~Queen B