maybe...

If you're writing about worksheets in the same workbook, you can group the
sheets (click on the first tab and ctrl-click on subsequent tabs). Then
File|page setup and change the headers/footers.

Remember to ungroup the sheets (just rightclick on any of the grouped sheets and
select ungroup sheets) or you may damage your workbook. Almost anything you do
to a one sheet (of a group) is done to the rest of the sheets in that group.



Tester wrote:

--

Dave Peterson

Thanks Dave, but no, I need to change headers and footers in over a hundred
different folders on a regular basis. As the headers/footers are all the
same, I need to be able to select all the folders I want to change, and
change as a batch.

"Dave Peterson" wrote:

maybe...

If you're writing about worksheets in the same workbook, you can group the
sheets (click on the first tab and ctrl-click on subsequent tabs). Then
File|page setup and change the headers/footers.

Remember to ungroup the sheets (just rightclick on any of the grouped sheets and
select ungroup sheets) or you may damage your workbook. Almost anything you do
to a one sheet (of a group) is done to the rest of the sheets in that group.



Tester wrote:

--

Dave Peterson

You could record a macro when you change one of the headers/footers. Tweak it
to limit it to only the stuff you need to do (delete the stuff that gets
recorded that you don't want to change).

Then you could modify that code to look at the worksheets in the workbooks that
you want updated.

Kind of like this:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wkbk As Workbook
Dim wks As Worksheet

myFileNames = Application.GetOpenFilename _
("Excel Files,*.xls", MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
For Each wks In wkbk.Worksheets
'fix that header/footer (your modified code here)
Next wks
wkbk.Close savechanges:=True
Next iCtr

End Sub

The multiselect:=true means that you can select multiple files (click on the
first, ctrl-click on subsequent) in the File|Open dialog.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




Tester wrote:

Thanks Dave, but no, I need to change headers and footers in over a hundred
different folders on a regular basis. As the headers/footers are all the
same, I need to be able to select all the folders I want to change, and
change as a batch.

"Dave Peterson" wrote:

maybe...

If you're writing about worksheets in the same workbook, you can group the
sheets (click on the first tab and ctrl-click on subsequent tabs). Then
File|page setup and change the headers/footers.

Remember to ungroup the sheets (just rightclick on any of the grouped sheets and
select ungroup sheets) or you may damage your workbook. Almost anything you do
to a one sheet (of a group) is done to the rest of the sheets in that group.



Tester wrote:

--

Dave Peterson


--

Dave Peterson

Thanks for the help Dave. I am afraid that I have had very little experience
with Visual Basic, but I finaly made your program work with my recorded
macro. This has saved me a great deal of work. I am still having a problem
though, as the Header/Footer is restricted to the first sheet in the workbook
only. The program appears to select all sheets, but the Headers are not there.
I have also tried recording a macro selecting all sheets as in your first
reply. This works when done individualy, but not when applying the macro. The
sheets are selected as in your program, but again the Header is not applied.
Any sugestions?

"Dave Peterson" wrote:

You could record a macro when you change one of the headers/footers. Tweak it
to limit it to only the stuff you need to do (delete the stuff that gets
recorded that you don't want to change).

Then you could modify that code to look at the worksheets in the workbooks that
you want updated.

Kind of like this:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wkbk As Workbook
Dim wks As Worksheet

myFileNames = Application.GetOpenFilename _
("Excel Files,*.xls", MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
For Each wks In wkbk.Worksheets
'fix that header/footer (your modified code here)
Next wks
wkbk.Close savechanges:=True
Next iCtr

End Sub

The multiselect:=true means that you can select multiple files (click on the
first, ctrl-click on subsequent) in the File|Open dialog.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




Tester wrote:

Thanks Dave, but no, I need to change headers and footers in over a hundred
different folders on a regular basis. As the headers/footers are all the
same, I need to be able to select all the folders I want to change, and
change as a batch.

"Dave Peterson" wrote:

maybe...

If you're writing about worksheets in the same workbook, you can group the
sheets (click on the first tab and ctrl-click on subsequent tabs). Then
File|page setup and change the headers/footers.

Remember to ungroup the sheets (just rightclick on any of the grouped sheets and
select ungroup sheets) or you may damage your workbook. Almost anything you do
to a one sheet (of a group) is done to the rest of the sheets in that group.



Tester wrote:

--

Dave Peterson


--

Dave Peterson

This portion loops through all the worksheets in the workbook:

For Each wks In wkbk.Worksheets
'fix that header/footer (your modified code here)
Next wks

If you don't need that portion, just write your code to fix the single sheet:

wkbk.worksheets("sheet1")...

or if it's always the leftmost:
wkbk.worksheets(1)....



Tester wrote:

Thanks for the help Dave. I am afraid that I have had very little experience
with Visual Basic, but I finaly made your program work with my recorded
macro. This has saved me a great deal of work. I am still having a problem
though, as the Header/Footer is restricted to the first sheet in the workbook
only. The program appears to select all sheets, but the Headers are not there.
I have also tried recording a macro selecting all sheets as in your first
reply. This works when done individualy, but not when applying the macro. The
sheets are selected as in your program, but again the Header is not applied.
Any sugestions?

"Dave Peterson" wrote:

You could record a macro when you change one of the headers/footers. Tweak it
to limit it to only the stuff you need to do (delete the stuff that gets
recorded that you don't want to change).

Then you could modify that code to look at the worksheets in the workbooks that
you want updated.

Kind of like this:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wkbk As Workbook
Dim wks As Worksheet

myFileNames = Application.GetOpenFilename _
("Excel Files,*.xls", MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
For Each wks In wkbk.Worksheets
'fix that header/footer (your modified code here)
Next wks
wkbk.Close savechanges:=True
Next iCtr

End Sub

The multiselect:=true means that you can select multiple files (click on the
first, ctrl-click on subsequent) in the File|Open dialog.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




Tester wrote:

Thanks Dave, but no, I need to change headers and footers in over a hundred
different folders on a regular basis. As the headers/footers are all the
same, I need to be able to select all the folders I want to change, and
change as a batch.

"Dave Peterson" wrote:

maybe...

If you're writing about worksheets in the same workbook, you can group the
sheets (click on the first tab and ctrl-click on subsequent tabs). Then
File|page setup and change the headers/footers.

Remember to ungroup the sheets (just rightclick on any of the grouped sheets and
select ungroup sheets) or you may damage your workbook. Almost anything you do
to a one sheet (of a group) is done to the rest of the sheets in that group.



Tester wrote:

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson