Can I change Headers and footers in selected Reports at the same .

[Dave Peterson]

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:

 

[Tester]

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]

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]

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]

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)....

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?

 

[Tester]

I am afraid I did not make myself clear last time Dave. I do want to have
headers and footers on each sheet of every workbook. With my very limited
knowledge of Visual Basic, I can see that your code calls for every sheet on
each workbook to be opened, and the header/ footer code applied.
Unfortunatly, this doesn't happen for me. Watching carefully while the
program is running, I see that all sheets after sheet one on each workbook is
not highlighted, so the header/footer is only applied to the first sheet in
each workbook.

 

[Dave Peterson]

I'm guessing that it was something that happened after you merged that original
shell and your macro that changed the header/footer.

First, ignore the followup and go back to the original suggestion.

But my guess is that your code is working on the activesheet (if you recorded
the macro, this is a common problem).

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

Replace that 'fix that header... line with your code.

But if you have any:
with activesheet
change them to
with wks

In fact, if you have any activesheet's at all, replace them all with wks.

Another option...

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

By adding that wks.activate line, it may be sufficient.

If all else fails, you're going to have to post the code you're using.

I am afraid I did not make myself clear last time Dave. I do want to have
headers and footers on each sheet of every workbook. With my very limited
knowledge of Visual Basic, I can see that your code calls for every sheet on
each workbook to be opened, and the header/ footer code applied.
Unfortunatly, this doesn't happen for me. Watching carefully while the
program is running, I see that all sheets after sheet one on each workbook is
not highlighted, so the header/footer is only applied to the first sheet in
each workbook.

 

[Tester]

Dave your were spot on! I changed the Activesheets to Wks. and it now workd
fine. Thanks once again for all the help, you have saved me a great deal of
work