multiple headers in excel
 

I have a document which when printed needs 4 unique copies. Only the
information on the header changes, and the rest of the document stays the
same. Is there a way to assign multiple headers? Or, if i have to make
individual worksheets for all pages, is there a way to hide all but one of
them from view in the workbook but still print all four?

multiple headers in excel
 

A macro similar to the one below would print 4 times with a different header
each time.

This was not tested, but it's rather straight forward.

Sub PrintFourCopies()
'
' PrintFourCopies Macro

'Object variables for the curent workbook
'and sheet 1 in the workbook
Dim wb As Workbook
Dim ws As Worksheet

'String variables for each of the four headers
Dim strHeader1 As String
Dim strHeader2 As String
Dim strHeader3 As String
Dim strHeader4 As String
'Variables for the array that holds each header value
Dim vararray As Variant
Dim varItems As Variant

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

'Assign each header variable its respective value
strHeader1 = "I'm the header for copy number 1."
strHeader2 = "I'm the header for copy number 2."
strHeader3 = "I'm the header for copy number 3."
strHeader4 = "I'm the header for copy number 4."
'Assign all the header variables to the array
vararray = Array(strHeader1, strHeader2, strHeader3, _
strHeader4)
varItems = vararray

'loop through the array changing the header with
'each printing.
For Each varItems In vararray
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = varItems
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Collate:=True
Next varItems

'standard housekeeping
Set wb = Nothing
Set ws = Nothing
End Sub

--
Kevin Backmann


"mike" wrote:

I have a document which when printed needs 4 unique copies. Only the
information on the header changes, and the rest of the document stays the
same. Is there a way to assign multiple headers? Or, if i have to make
individual worksheets for all pages, is there a way to hide all but one of
them from view in the workbook but still print all four?

multiple headers in excel
 

Something like this:

Private Sub CommandButton1_Click()

With Worksheets("sheet1")
.PageSetup.LeftHeader = "Individual title 1"
.PrintOut
.PageSetup.LeftHeader = "Individual title 2"
.PrintOut

End With
End Sub

On the VBA help look for the keyword "pagesetup".

Regards

Hans

multiple headers in excel
 

Thanks to both of you! i will try both of these solutions. In the meantime
i fixeed the problem with a simple data validation list but it's rather
clunky so I'll try the more complex solutions.

Thanks again,

mike

"flummi" wrote:

Something like this:

Private Sub CommandButton1_Click()

With Worksheets("sheet1")
.PageSetup.LeftHeader = "Individual title 1"
.PrintOut
.PageSetup.LeftHeader = "Individual title 2"
.PrintOut

End With
End Sub

On the VBA help look for the keyword "pagesetup".

Regards

Hans