Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To get the same header,footer and sheet titles across multiple sheetsof a workbook.
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").Addre ss ActiveSheet.PageSetup.PrintTitleColumns = ActiveSheet.Columns("A:C").Address End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To get the same header,footer and sheet titles across multiple she
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
header/footer for the whole workbook | Excel Worksheet Functions | |||
How do I set up a single header for a multiple sheet workbook? | Excel Discussion (Misc queries) | |||
How to save a custom header/footer on ea. sheet of a workbook? | Excel Worksheet Functions | |||
Give multiple charts on a worksheet/workbook same header or footer | Excel Discussion (Misc queries) | |||
Give multiple charts on a worksheet/workbook same header or footer | Charts and Charting in Excel |