select all sheets?
 

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

Thanx Spencer.
I am actually using 4 of the six available headers/footers on each page.
What's the best "with/endwith" stmt to use in conjunction with your code?

- Mike


"Spencer" wrote:

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

For some additional functionality, if you have a range named "LeftHeader"
you can insert this statement and make the left header a reference to a cell
range's text instead of "hard coding" it.

Dim sht As Worksheet
Dim lftHdrRng As String

lftHdrRng = Range("LeftHeader").Text

For Each sht In ActiveWorkbook.Worksheets
With sht.PageSetup
.LeftHeader = lftHdrRng
.CenterHeader = "CENTER HEADER TEXT"
.RightHeader = "RIGHT HEADER TEXT"
.LeftFooter = "LEFT FOOTER TEXT"
.CenterFooter = "CENTER FOOTER TEXT"
.RightFooter = "RIGHT FOOTER TEXT"
End With
Next sht




"MikeF" wrote:

Thanx Spencer.
I am actually using 4 of the six available headers/footers on each page.
What's the best "with/endwith" stmt to use in conjunction with your code?

- Mike


"Spencer" wrote:

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

Looks great, thank you.

"Spencer" wrote:

For some additional functionality, if you have a range named "LeftHeader"
you can insert this statement and make the left header a reference to a cell
range's text instead of "hard coding" it.

Dim sht As Worksheet
Dim lftHdrRng As String

lftHdrRng = Range("LeftHeader").Text

For Each sht In ActiveWorkbook.Worksheets
With sht.PageSetup
.LeftHeader = lftHdrRng
.CenterHeader = "CENTER HEADER TEXT"
.RightHeader = "RIGHT HEADER TEXT"
.LeftFooter = "LEFT FOOTER TEXT"
.CenterFooter = "CENTER FOOTER TEXT"
.RightFooter = "RIGHT FOOTER TEXT"
End With
Next sht




"MikeF" wrote:

Thanx Spencer.
I am actually using 4 of the six available headers/footers on each page.
What's the best "with/endwith" stmt to use in conjunction with your code?

- Mike


"Spencer" wrote:

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

You've been extremely helpful.
Although I've just ran into a kink -- instead of select ALL sheets, will
need to run the subroutine only on SELECTED sheets.
Any ideas?
Thanx in advance.
- Mike

"Spencer" wrote:

For some additional functionality, if you have a range named "LeftHeader"
you can insert this statement and make the left header a reference to a cell
range's text instead of "hard coding" it.

Dim sht As Worksheet
Dim lftHdrRng As String

lftHdrRng = Range("LeftHeader").Text

For Each sht In ActiveWorkbook.Worksheets
With sht.PageSetup
.LeftHeader = lftHdrRng
.CenterHeader = "CENTER HEADER TEXT"
.RightHeader = "RIGHT HEADER TEXT"
.LeftFooter = "LEFT FOOTER TEXT"
.CenterFooter = "CENTER FOOTER TEXT"
.RightFooter = "RIGHT FOOTER TEXT"
End With
Next sht




"MikeF" wrote:

Thanx Spencer.
I am actually using 4 of the six available headers/footers on each page.
What's the best "with/endwith" stmt to use in conjunction with your code?

- Mike


"Spencer" wrote:

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike

select all sheets?
 

For Each sht In ActiveWindow.SelectedSheets


Gord Dibben MS Excel MVP


On Wed, 7 Mar 2007 14:14:03 -0800, MikeF
wrote:

You've been extremely helpful.
Although I've just ran into a kink -- instead of select ALL sheets, will
need to run the subroutine only on SELECTED sheets.
Any ideas?
Thanx in advance.
- Mike

"Spencer" wrote:

For some additional functionality, if you have a range named "LeftHeader"
you can insert this statement and make the left header a reference to a cell
range's text instead of "hard coding" it.

Dim sht As Worksheet
Dim lftHdrRng As String

lftHdrRng = Range("LeftHeader").Text

For Each sht In ActiveWorkbook.Worksheets
With sht.PageSetup
.LeftHeader = lftHdrRng
.CenterHeader = "CENTER HEADER TEXT"
.RightHeader = "RIGHT HEADER TEXT"
.LeftFooter = "LEFT FOOTER TEXT"
.CenterFooter = "CENTER FOOTER TEXT"
.RightFooter = "RIGHT FOOTER TEXT"
End With
Next sht




"MikeF" wrote:

Thanx Spencer.
I am actually using 4 of the six available headers/footers on each page.
What's the best "with/endwith" stmt to use in conjunction with your code?

- Mike


"Spencer" wrote:

no need to select.

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.PageSetup.LeftHeader = "HEADER TEXT")
Next sht

"MikeF" wrote:

I have a small macro that reworks the headers and footers in Excel.
But it works only on the active sheet.

What code can I put before it to "select all sheets" in the workbook?

Thanx,
- Mike