To get the same header,footer and sheet titles across multiple sheetsof a workbook.

[Subodh]

Dear Friends,
I want a workbook to have the same header (as some cell value of a
sheet in workbook) and similarly same titles (rows and columns titles)
in different sheets. I reached to the following code. It worked well
for the header (and also footer) but it didn't work for the rows to
repeat at top or columns to repeat. It only works for the active
sheet. How can i make my work done?? The main problem i think is to
make ActiveSheet.PageSetup.PrintTitleRows work across multiple sheets.
Please help me. I have the code attached here for yur reference.

'this is the workbook code
Private Sub Workbook_BeforePrint(Cancel As Boolean)
getcellheader
End Sub

'this is the module code
Sub getcellheader()
Set datasht = ThisWorkbook.Sheets("DataSheet")
With ActiveSheet.PageSetup
.RightHeader = ""
.LeftHeader = datasht.Range("a9").Text '
.CenterHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
ActiveSheet.PageSetup.PrintTitleRows =
ThisWorkbook.Sheets("DataSheet").Rows("1:3").Address
ActiveSheet.PageSetup.PrintTitleColumns =
ActiveSheet.Columns("A:C").Address
End Sub

 

[OssieMac]

To the best of my knowledge, Rows to repeat at top and Columns to repeat at
left must belong to the actual worksheet being printed. You cannot use the
rows or columns from another worksheet. However, you can use a value from
another worksheet when setting a Header/Footer because these ar only string
values.

Also, you can iterate through the sheets in the workbook for Page Setup. The
example below demonstrates iterating through the worksheets and using Select
Case so that you can limit the setup to the required worksheets. If all
worksheets required then delete the lines Select Case, Case and End Select.



I like Select Case in lieu of If statements because to add more worksheet
all you have to do is insert the sheet name between double quotes and a comma
between each worksheet in the Case statement as per the example.

Sub getcellheader()
'This code sets LeftHeader of all sheets
'to same value of Range("A9") of Sheet1.

Dim dataSht As Worksheet
Dim wsSht1 As Worksheet

'Edit "Sheet1" to your sheet name
'with cell A9 value for LeftHeader.
Set wsSht1 = Sheets("Sheet1")

For Each dataSht In ThisWorkbook.Sheets

Select Case dataSht.Name
Case "Sheet1", "Sheet2", "Sheet3"
With dataSht.PageSetup
.RightHeader = ""
'Following line sets LeftHeader
'to same value in all sheets.
.LeftHeader = wsSht1.Range("A9").Text
.CenterHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

With dataSht.PageSetup
.PrintTitleRows = dataSht.Rows("1:3").Address
.PrintTitleColumns = dataSht.Columns("A:C").Address
End With
End Select

Next dataSht

End Sub