Is there a way to display a cell information in the header of multiple sheets?

**elgourmet** · 07-19-2010, 04:58 AM

hello there

i'm new to this forum but have been reading here for a while. I've also tried the search function but it didnt come up with a proper solution, so I hope it's ok if I immediately start off with a question

:

I have an excel file with several sheets.
Every sheet has a header. What I am trying to achieve is to change the header information of all headers by just changing the value of a cell in another sheet.

i'm not sure if my explanation was clear enough, so here is an example:
Sheet1, Sheet2, Sheet3 have the same header:
"Annual Report 2010"
i want 2010 to become 2011 if i change a certain cell in Sheet4

here is what i've come up with so far:

Please Login or Register  to view this content.

this code works fine, but it only changes the header for the active sheet AND it does change the whole header. is there a way to just change a part of the header? like just the year of "Annual Report 2010"?

any tips, help or advices would be very much appreciated!

**teylyn** · 07-19-2010, 05:10 AM

Hello and welcome to the forum.

Why not just use a formula in sheets 1 to 3, something like

="Annual Report "&Sheet4!A20

That does not even require VBA.

cheers,

**elgourmet** · 07-19-2010, 05:13 AM

the problem is that formulas do not work in headers. they just show up as text. or am i missing something here?

**teylyn** · 07-19-2010, 05:23 AM

Oh, I see what you mean now. You can cycle through all your sheets like this:

Please Login or Register  to view this content.

cheers,

**elgourmet** · 07-19-2010, 05:50 AM

thank you very very much, teylyn!
this helped a lot!

i've two more small questions though, if you don't mind

a) how can i apply this function only to selected sheets? i cant find the syntax in the documentation, but this shouldn't be hard to achieve?
it may be something like For ActiveWorkbook.Sheets("Sheet1,Sheet2") instead of "For Each ws In ActiveWorkbook.Worksheets" but it doesnt work out :/

b) i'm afraid this is not possible at all, but i thought i'd still ask: is it possible to insert the given text Annual Report 2010 in line 2 of the header? it's just because the header often has 2 lines. First line is something like "Cars" or "Offices" and line 2 is Annual Report 2010. So line 1 is variable.... :-/

**teylyn** · 07-19-2010, 06:18 AM

try this:

click the first sheet tab, then hold down the Ctrl key and click the other sheet tabs for which you want to change the header. Then run this code

Please Login or Register  to view this content.

The portion of the header up to the first line break will be retained. Everything after the first line break will be replaced with "Annual Report " and the number in A20 on sheet 4.

Will that work for you?

**elgourmet** · 07-19-2010, 08:42 AM

this is absolutely amazing, thank you so much!
btw, do you know if there is another way to define certain sheets instead of clicking on them? i would prefer a code-only version, but if this is the only way its also ok

thanks again!

**teylyn** · 07-19-2010, 05:34 PM

If you want to define the sheets in code, then try this

Please Login or Register  to view this content.

**elgourmet** · 07-20-2010, 01:51 AM

thank you so much, teylyn! you really made my day!

**teylyn** · 07-20-2010, 02:35 AM

gern gescheh'n! :-))

**snb** · 07-20-2010, 04:16 AM

The combination of an Array and For Each is also possible:

Please Login or Register  to view this content.

**teylyn** · 07-20-2010, 06:20 AM

snb, the shortest code is not always the best code, especially if the user does not have much VBA experience and wants to be able to understand and maintain the code in a year's time.

Taking a few more steps, clearly documented and readable for a VBA beginner, is probably preferable in this case.

Excelforum is not about providing a solution with as few characters as possible, although some people seem to take this approach.

I prefer a solution that the OP can understand and maintain over a slick nested statement that they don't have a clue about how it works.

Is there a way to display a cell information in the header of multiple sheets?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Re: Is there a way to display a cell information in the header of multiple sheets?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions