headers from cell value

[colleen]

I have spreadsheet with over 30 worksheets, for each sheet I print out I have several headers which I have to use..to make my life simpler HOW can i enter the header contents in a cell in sheet1, which will automatically enter that into the headers on all the sheets, then all I have to do is to change that cell in sheet1 with a different header content and it just replaces the same..HELP

 

[Damon Ostrander]

Hi colleen,

Probably the easiest way to do this is to edit the header cells as a "3-d range". Such a range has "depth" in that it includes other worksheets than the one that is active. To do a 3-d cell edit, select ALL the sheets you want to be changed when you edit a cell on the active sheet. To select all worksheets, simply right-click on the tab of the worksheet you want to type the heading into, then select Select All Sheets. You can of course select just certain sheets by holding down the Ctrl key as you left-click on the worksheet tabs.

Once the sheets are selected, anything you enter in a cell in the active worksheet will be entered into the corresponding cells on all the other selected sheets.

Be careful! If you forget to unselect the sheets when you make changes intended for only one sheet, you may inadvertently wipe out data in the other sheets. Of course, an Undo will usually get you out of this if you discover the problem soon enough.

 

[colleen]

thanks damon, that really helps but what I really want to have a worksheet in which I can enter in a cell what I want to enter as a header on the other sheets. So I can just change the cell content and all the other sheets header is updated in one swoosh (for lack of a better word)...Col

 

[Andrew Poulsom]

Suppose your header is in cell A1 of Sheet1.

In cell A1 of Sheet2 (or wherever your header is) type the formula:

=Sheet1!$A$1

Copy the formula to wherever the header is on your other sheets.

 

[lenze]

Place this or similar code in the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = Sheet1.Range("$A$1").Text
End With
End Sub

 

[colleen]

thanks a lot for your help..now to further my query, my header consist of several cell contents i.e. $a$1 with company name $b$1 with address. So how do I go about incorporating both the company name and address in the header..col

 

[lenze]

You can also define the centerheader and right header, or add more than one cell reference to a header, as below

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = Sheet1.Range("$A$1").Text & Chr(13) & Sheet1.Range("$B$1").Text
.RightHeader = Sheet1.Range("$A$3").Text
End With
End Sub

Note: The CHR(13) is a line break
This message was edited by lenze on 2002-09-03 09:35

 

[Andrew Poulsom]

If it's a true print header (Lenze) then:

.LeftHeader = Sheet1.Range("$A$1").Text & " " & Sheet1.Range("$B$1").Text

If it's only text on the sheet (me), then:

=Sheet1!$A$1&" "&Sheet1!$B$1

 

[colleen]

Also lenze how do I go about starting a NewWorkBook module..colleen

 

[lenze]

On 2002-09-03 09:35, colleen wrote:
Also lenze how do I go about starting a NewWorkBook module..colleen

The ThisWorkBook Module already exists when you open a WorkBook. You can access it throught the Project Explorer in the VBA Editor, or the easiest way is to Right Click on the Excel Icon To the left of the Word file on the Menu Bar and choose View Code
This message was edited by lenze on 2002-09-03 09:42