vba : Headers not displaying in all sheets
 

Have a workbook populated by several sheets copied from other book. After
they are copied, I loop thru the .Sheets collection, adding headers to each
sheet.
For some reason, the headers are only displaying on some sheets. Has anyone
ever run across this problem before? Sheet property that isn't set properly?
Any Ideas?

Thanks - Sean


Public Sub HeaderSet()
Dim sht As Worksheet

For Each sht In Workbooks(s_Author & ".xls").Sheets
If sht.Type = xlWorksheet Then
sht.PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
sht.PageSetup.CenterHeader = _
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
sht.PageSetup.RightHeader = "&D"
End If
Next sht

End Sub

also tried :

Public Sub HeaderSet()
Dim i As Integer

For i = 1 To Workbooks(s_Author & ".xls").Sheets.Count
If Workbooks(s_Author & ".xls").Sheets(i).Type = xlWorksheet Then

Workbooks(s_Author & ".xls").Sheets(i).PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
Workbooks(s_Author & ".xls").Sheets(i).PageSetup.CenterHeader =
_
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
Workbooks(s_Author & ".xls").Sheets(i).PageSetup.RightHeader =
"&D"
Else

Workbooks(s_Author & ".xls").Charts(i).PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
Workbooks(s_Author & ".xls").Charts(i).PageSetup.CenterHeader =
_
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
Workbooks(s_Author & ".xls").Charts(i).PageSetup.RightHeader =
"&D"
End If
Next i

End Sub

vba : Headers not displaying in all sheets
 

Loop through the worksheets collection. If you are going to Dim sht as
Worksheet, there is no reason not to.

Public Sub HeaderSet()
Dim sht As Worksheet
Dim sh as Worksheet
set sh = Activesheet
For Each sht In Workbooks(s_Author & ".xls").WorkSheets
' added just to check what is in the collection
' that gets processed
msgbox Sht.Name & " is worksheet"
sht.Activate
sht.PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
sht.PageSetup.CenterHeader = _
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
sht.PageSetup.RightHeader = "&D"
Next sht
sh.Select
End Sub

--
Regards,
Tom Ogilvy


"SeanMc" wrote:

Have a workbook populated by several sheets copied from other book. After
they are copied, I loop thru the .Sheets collection, adding headers to each
sheet.
For some reason, the headers are only displaying on some sheets. Has anyone
ever run across this problem before? Sheet property that isn't set properly?
Any Ideas?

Thanks - Sean


Public Sub HeaderSet()
Dim sht As Worksheet

For Each sht In Workbooks(s_Author & ".xls").Sheets
If sht.Type = xlWorksheet Then
sht.PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
sht.PageSetup.CenterHeader = _
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
sht.PageSetup.RightHeader = "&D"
End If
Next sht

End Sub

also tried :

Public Sub HeaderSet()
Dim i As Integer

For i = 1 To Workbooks(s_Author & ".xls").Sheets.Count
If Workbooks(s_Author & ".xls").Sheets(i).Type = xlWorksheet Then

Workbooks(s_Author & ".xls").Sheets(i).PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
Workbooks(s_Author & ".xls").Sheets(i).PageSetup.CenterHeader =
_
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
Workbooks(s_Author & ".xls").Sheets(i).PageSetup.RightHeader =
"&D"
Else

Workbooks(s_Author & ".xls").Charts(i).PageSetup.LeftHeader = _
"Page No. " & "&P" & " of &N"
Workbooks(s_Author & ".xls").Charts(i).PageSetup.CenterHeader =
_
s_Author & Chr(10) & "&F" & Chr(10) & "&A"
Workbooks(s_Author & ".xls").Charts(i).PageSetup.RightHeader =
"&D"
End If
Next i

End Sub