So refreshing to have you ask a question for a change rather than answering
them! <big g> An opportunity to give back...



I don't know why you're getting that particular error - I would expect an
error 91 - Object variable or With block  variable not set - but I notice
you're not selecting the first worksheet  initially, so I would think it's
very possible it's trying to select PAST the last worksheet.



So, does putting:

Application.ActiveWorkbook.Worksheets(1).Select

just before the For/Next loop help?

I had an "Sheets(1).Activate" right before the loop started, but when the
code started failing in the middle of the workbook, we commented that out,
so that we could start at the currently selected sheet.

Yes, an Error 91 does pop-out when they get to the end of the workbook,
which is why I handled it in the EH. The thing is, that this particular
error occurs in the middle of the workbook. <shrug> I first thought it was
something wrong with the particular sheet, however, you can restart the
macro on the sheet previous, and the code will run as expected until it gets
to the end of the workbook. <sigh> Nothing for me to be able to say "Ah HA"
about.

Just make a subtle change in your code.

Remove " ActiveSheet.Next.Select" from your program. At the beginning of
your For Loop add  this: " Sheets(counter).Select"

This will start at index 1 (each sheet is indexed starting at 1) and
work its way to the maximum amount of sheets  within the workbook.

but that change didn't fix the error. <scratching head>

Not that it would help solve the problem but... Just a thought... Have you
tried reducing the number of worksheets.. Binary chop say.. To see if
there's a number at which everything works ok?

as far as I can tell, the code works in
general.

Upload the file and let him have a look at it.
Sounds like something funky going on with that one sheet.

I think I may have figured it out, it seems as though the sheets  which throw
up the error have a space in the footer. The only clue I had was that (none)
was not displayed in the custom footer when looking at the sheet.

So, how to code around that? If there is a space or multiple spaces, I want
t replace the text with my text. However, if there is actually text in the
footer, then I want to append my footer to the existing footer.

I'm not sure why spaces would be causing the problem, but if you think
it is then you might want to try trimming the text. Try something like
this...

Dim str as String

With ActiveSheet.PageSetup
If Len(.CenterFooter) > 0 Then
str = Trim(.CenterFooter) & " Your Text Here"
.CenterFooter = str
End if
End With

When I ran a test with spaces, both with and without surrounding text
the above worked just fine by replacing the spaces with the text, or
simply removing them from the surrounding text.

Since it takes a long time, Autosave wouldn't be trying to come in as
the macro's running would it?

How about avoiding the sheet activations?:

On Error GoTo EH
Counter = 0
For Each sht In Application.ActiveWorkbook.Worksheets
Counter = Counter + 1
With sht.PageSetup

...page setup stuff here

End With
'ActiveSheet.Next.Select (commented out)
Next sht
Exit_EH:
etc.

worked OK on 192 empty sheets  here (XL2003)


ps. I realise that your macro works from the currently active sheet to
the last sheet, whereas mine does all the sheets regardless, but it
could help in diagnosis of the fault. If there ends up being no fault,
then you could perhaps pop in an IF statement of the ilk:

With sht.PageSetup
If .CenterFooter <> "CONFIDENTIAL" & Chr(10) & "Subject to Protective
Order" & Chr(10) & "USDC-KS 05-1368" Then
...page setup stuff here
End If
End With

Or if you want to only affect sheets from the active sheet add  an IF
which sets a flag when the active sheet is reached:

ActiveSheetReached = False
For Each sht In Application.ActiveWorkbook.Worksheets
If sht = ActiveSheet Then activeSheetReached = True
If activeSheetReached Then
Counter = Counter + 1
With sht.PageSetup
...page setup stuff herre
End With
End If
(you might want to put the Counter increment line elsewhere)

If you want the sheet with the error on it to become the active sheet
would a:
sht.activate
in the error handler do it?