Header & Footers

[tf37]

I have Excel 2000.
1 workbook with 70 worksheets.
If I select all worksheets and go to header & footers, is there a way to add in the header the name of each worksheet, and have all worksheets pick up there individual name?
Like: Page 1 of ? in the footer.
I'd hate to have to create a custom header for all 70 worksheets
Thanks gang,
Terry

 

[Peter_SSs]

Can't remember back to Excel 2000 but certainly versions since you can put the following in the custom header (with all sheets selected)
&[tab]

 

[tf37]

That did the trick, many thanks.
Now if I could figure out how to put a space between "Sheet1" to "Sheet 1" so it is not run together, but no worries, it works for me
Cheers,
Terry

 

[Peter_SSs]

Now if I could figure out how to put a space between "Sheet1" to "Sheet 1" so it is not run together, ..

Can you explain in a bit more detail just what you have and what you want to do?

 

[tf37]

It really isn't a worry, but for what its worth
I have 70 worksheets, all named sheet1 to sheet70.
I see how it picks up the sheet name now with the &[tab], tab being the sheet name...right?
So if I rename all my worksheets that would fix it. To much hassle for me, and it works.
It is for my jury wheel of possible folks that will be selected when we have a jury trial.
Ideally, it would be great for the header to say:
Jury List 2011 - easy enough 1st row of the header
Sheet # of 70, or Group # of 70
I take out of 15,000 names, run a rand sort formula, sort that result in ascending order, pick 7000 names and put 100 names in each worksheet.
Terry

 

[Peter_SSs]

If you just want to rename
Sheet1, Sheet2, .... , Sheet70 to
Sheet 1, Sheet 2, ... , Sheet 70
then this macro should do that for you.

Test in a copy of your workbook. Post back if you need help with how to implement it.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RenameSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        <SPAN style="color:#00007F">If</SPAN> ws.Name <SPAN style="color:#00007F">Like</SPAN> "Sheet#*" <SPAN style="color:#00007F">Then</SPAN><br>            ws.Name = Replace(ws.Name, "Sheet", "Sheet ", 1, 1)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[tf37]

Work like a charm.
I continue to learn something all the time I visit the Excel board.
Wish I would have thought of it myself. Unfortunately, I do not think the built in help goes that deep into things at times, or my search was off base.
I am finished, and thank you very much for your time & efforts to help.

As a side note, I am not sure where your at in OZ, but hopefully not troubled by all the flooding, cyclones and such that have struck. I have been to Melbourne several times when with ANZ, and think it is just grand.
Cheers mate,
Terry