Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Freeze column headers for ledger sheets | Excel Discussion (Misc queries) | |||
can you set up global headers & footers for all sheets in a file? | Excel Discussion (Misc queries) | |||
Can I duplicate headers and footers on multiple sheets? | Excel Worksheet Functions | |||
how do i change headers on all workbook sheets globally? | Excel Discussion (Misc queries) | |||
how do I copy excel sheets with headers | Excel Worksheet Functions |