Re: Can I change Headers and footers in selected Reports at the sa

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 active*** (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 Work***

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 active***
change them to
with wks

In fact, if you have any active***'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.
Tester wrote:
>
> I am afraid I did not make myself clear last time Dave. I do want to have
> headers and footers on each *** of every workbook. With my very limited
> knowledge of Visual Basic, I can see that your code calls for every *** 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 *** in
> each workbook.
>
> "Dave Peterson" wrote:
>
> > 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 ***:
> >
> > 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 *** 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 Work***
> > > >
> > > > 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
> >

--

Dave Peterson
.

Quantcast