Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select the same cell in sheets 2 & sheets 3 | Excel Programming | |||
How to Select All sheets | Excel Programming | |||
Select first 3 sheets | Excel Programming | |||
sheets.select | Excel Programming | |||
select sheets by name - how? | Excel Programming |