Yeah, I said there may be issues, didn't I? :o)

Anyway, since I do not have your workbook in front of me it's a bit tricky
to know exactly what causes this issue, but as you say -- it's probably
caused by our attempt to refer to a sheet that does not exist, which is
probably due to an issue with GetSheetName() or with the collection of names.

Why not do like this: go into the code, and put a break point (press F9) at
the first line of the function GetSheetName(). Then you can single-step
through the function and make sure the sheet names are sensible (you need the
Debug toolbar to be active in the VBA Editor). In single-step mode you can
continuously check the values of your variables in VBA's so-called Locals
Window (open it up with View Locals Window)

Alternatively, put the following lines just before the "End Function" line
of your GetSheetName() function:

Debug.Print "GetSheetName():"
Debug.Print " sRefTo = " & sRefTo
Debug.Print " iPosEq = " & CStr(iPosEq)
Debug.Print " iPosEx = " & CStr(iPosEx)
Debug.Print " GetSheetName = " & GetSheetName

....Then run the program and check the output in the Immediate window. You
should always get a sensible sheet name back.

Cheers,
/MP



"rm81" wrote:

Hi there - me again!

That simply means that you've got a bunch of defined names, some of which
are no longer okay -- the #REF! tells us that the definitions are broken,
most likely because you've deleted the sheets which those definitions were
using (when you delete sheets the associated named ranges will not be
automatically removed). To fix this, just go to the Insert Name Define...
dialogue, and kill all the defined names that are no longer in use, i.e., the
broken ones.

That's what I figured so have done that and it all looks to be in order now.

So, let's have a look at the actual code then (and I'm not saying it's bug
free, but if there are any problems I'm sure we'll be able to figure them out
:o)

Wow - looking at all that code - I think I understand about 25% of it only!!
But seeing as I am pushed for time, I think I'll take time out after
completing the project to go through all of it.

So I transplanted the code into the application but a slight hitch has come
up. When it runs through the following :

Set oSheet = Worksheets(GetSheetName(oName.RefersTo))
oSheet.PageSetup.LeftFooter = StripSheetName(oName.Name)
oName.RefersToRange.PrintOut Preview:=True

it then refers and goes through the

Private Function GetSheetName(sRefTo As String)

but at the end of it, it says "run-time error 9" (well, to be honest it
actually said "erreur d'execution 9" - am working in France so have an OS in
french; not the easiest!) which having looked up means "Subscript Out Of
Range".
I read that this means that one of the named sheets doesn't exist as given
in the Named sheets routine. Unfortunately as I don't understand all of the
code, I can't resolve this error!! Do you have an idea at all?
Many thanks! :)
rm81