wohoooooo!! once again...genius! have just been jumping for joy around my
office. Phew, thanks a million - was getting quite demoralised with this
whole vba malarky.

Just one last question and I promise to leave you in peace!
The ranges print in alphabetical order and not in the order of my actual
document - i.e. "COMMANDE" then "CONDITIONNEMENT", then "EMBALLAGE" etc
instead of "COMMANDE" then "ETIQUETTES DESTINATAIRE" then "ETIQUETTES
PRODUIT" etc.

Is there a way of fixing that - I think I vaguely remember reading in the
Help that in the Names collection, Names are in alphabetical order - is that
why then? I suppose it would be even more complicated to undo that
alphabetical order thing. If so then it'll more than do as it is for the
moment - it's just a bit time-consuming for the application users to put the
document in order once it's printed out.

Once again sorry and thanks for all your help - you have been my saviour!
Right I'm off to have a nice cold beer. I'll drink to your health!
Have a good evening :)
rm81

"Mat P:son" a écrit :

"rm81" wrote:

hey,

so did the debug thing and it came up with what seems to me is a perfectly
sensible name - 2138085 :
GetSheetName():
sRefTo = ='2138085'!$A$1:$E$100
iPosEq = 1
iPosEx = 11
GetSheetName = '2138085'

Ouch, actually it seems as if there are little pesky quote characters as
well (') enclosing the actual sheet name (it's obvious when you look closely
at the output, which I obviously didn't -- ooops). So I suppose we have to
modify the code a bit:

GetSheetName = Mid$(sRefTo, iPosEq + 1, iPosEx - iPosEq - 1)

...will become:

GetSheetName = Mid$(sRefTo, iPosEq + 2, iPosEx - iPosEq - 3)

...and this will stop the quotes trickling through. To see the difference,
try running for example a little routine like this:

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("sheet1") ' This is OKAY
Set ws2 = Worksheets("'sheet1'") ' This will FAIL due to the single
quote chars
End Sub

A couple of questions though - the result of "sRefTo" is the name of the
sheet plus just the range for Range "COMMANDE" i.e. $A$1:$E$100 - is that
normal?

Yes, it is. Also note that you can retireve info about the range the defined
name is refering to in serveral other ways, that is, you don't necessarily
have to use RefersTo -- you can use RefersToLocal, RefersToR1C1,
RefersToRange etc. Why not have a look in the VBA Help, especially the "See
Also" section of RefersTo, where many alternatives are listed.

Secondly - what do "iPosEq" and "iPosEx" refer to/mean/do? And why is
iPosEx 11?

Sorry, I should really be more explicit when I write example code: iPosEq
and iPosEx refer to different positions in the string we're searching, using
InStr().

- iPosEq will point to the location of the Equal sign (if one is present in
sRefTo)
- iPosEx will point to the Exclamation sign (hopefully)

We then use Mid$ to retrieve the part of the sRefTo string that should
represent the name of the sheet. Read about Mid$ in the VBA Help -- it's a
pretty useful function...

Cheers,

It's me that should be cheers/thanking you!! I need to buy you a virtual
drink after all this!!
rm81

heheheh :o)

Well, how about "later", then? Or "ta da"?
/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