Prev: Full Script for Excel Spreadsheet Import Automation
Next: user to input date as "yyyy" in cell
From: OssieMac on 24 Feb 2010 01:32
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

--
Regards,

OssieMac


 | 
Pages: 1
Prev: Full Script for Excel Spreadsheet Import Automation
Next: user to input date as "yyyy" in cell