Renaming a range

[Guest]

Hi there,

Am a bit of a beginner with VBA bu thave managed to get my project to work.
However, I have come across a problem.

I have several named ranges :

Sub Nommer_les_plages()
'Ignore/excuse the French
Range("A1:E100").Name = "COMMANDE"
Range("A101:E200").Name = "ETIQUETTES_DESTINATAIRE"
Range("A201:E300").Name = "ETIQUETTES_PRODUIT"
Range("A301:E500").Name = "CONDITIONNEMENT"
Range("A451:E500").Name = "FEUILLE_COMPLEMENTAIRE"
Range("A501:E550").Name = "EMBALLAGE"
Range("A551:E600").Name = "VERIFICATION_DU_DOSSIER"
End Sub

I refer to certain ranges in order to copy and insert them v number of times
(v being a value in cell C12) into my worksheet i.e. :

With Workbooks("Procédures.xls").Sheets(1).Activate
Cells(12, 3).Activate
v = ActiveCell.Value
For i = 2 To v
'Copies the Conditionnement pages
Range("CONDITIONNEMENT").Select
Range("CONDITIONNEMENT").Copy
Selection.Insert Shift:=xlDown
Next
For i = 2 To v
'Copies the Etiquettes Produits pages
Range("ETIQUETTES_PRODUIT").Select
Range("ETIQUETTES_PRODUIT").Copy
Selection.Insert Shift:=xlDown
Next
Range("C66").Select
End With

Now say v = 3, the ranges ("CONDITIONNEMENT") and ("ETIQUETTES_PRODUIT") are
copied & pasted twice ABOVE the original ranges so in total I have three sets
of ("CONDITIONNEMENT") and ("ETIQUETTES_PRODUIT").

My desire would be to rename these ranges to something like
"CONDITIONNEMENT_1", "CONDITIONNEMENT_2" etc and then make these range names
appear as a footer when printed out.

Am I being foolishly and wildly over optimistic in thinking that this could
be possible???? Would be extremely grateful for a positive or even negative
response as at least it would put me out of my misery.

Cheers,
Rm81

 

[Guest]

Hi there,

I'm not entirely sure how you envisage the printing part of the problem to
work so I can't really help you there, but at least it won't be a major
problem to set cell range names programmatically:

Try for example this (and change as required for the ETIQUETTES_PRODUIT
part, of course):

For i = 2 To v
'Copies the Conditionnement pages
Range("CONDITIONNEMENT").Select
Range("CONDITIONNEMENT").Copy
Selection.Insert Shift:=xlDown
Selection.Name = "CONDITIONNEMENT_" & CStr(i)
Next i

 

[Guest]

Genius!! Thanks ever so much for your help - it works like a dream.
Best regards,
rm81


"Mat P:son" a écrit :

 

[Guest]

Genius!!

Thanks! (...from a humble genius )

Thanks ever so much for your help - it works like a dream.

I'm happy that it worked

 

[Guest]

With regards to the printing part - I had previously asked the same kind of
question but hadn't specified that the number of pages would be variable.
The person gave me this solution :

Sub ToPrintFooter()

Dim A As Integer
With Sheet1
For A = 1 To 5
With .PageSetup
.LeftFooter = "COMMANDE"
End With
.PrintOut From:=A, To:=A, Preview:=True
Next
For A = 6 To 8
With .PageSetup
.LeftFooter = "ETIQUETTES_DESTINATAIRE"
End With
.PrintOut From:=A, To:=A, Preview:=True
Next
For A = 9 To 10
With .PageSetup
.LeftFooter = "ETIQUETTES_PRODUIT"
End With
.PrintOut From:=A, To:=A, Preview:=True
Next
End With
End Sub

Do you have any idea as to how this could work with the variable range names
and pages?

Thankyou!

"Mat P:son" a écrit :

 

[Guest]

Actually, it's not as difficult as you may think: there are a few collections
available that contain the named ranges available in the Application,
Workbooks and Worksheets. This is useful if you want to add new named ranges
programmatically (e.g., "COMMANDE_123" correcponds to cells
"Sheet2!$A$1:$E$10" or whatever) but, above all, you can enumerate the ones
that have already been defined -- happy, happy, joy, joy.

So, what I'm saying is that the printing code you got is hardcoded to work
with certain cell ranges, which is a bit limiting. But luckily, we can now
fix this: by retrieving the Names collection, and iterating over all the
names you've already defined, you can easily get hold of their associated
cell range, build up the page layout, and print the pages. For more info, go
into the VBA editor and search for "Names" and "Name" in the VBA help.

Why not try printing out the defined names into the Immediate window of the
VBA editor using the following code snippet, just to see what you've got
available (since we're accessing the Names collection of the Application
object we'll get all the names defined for the active workbook):

Debug.Print "My defined name ranges are..."
Dim oName As Name
For Each oName In Application.Names
Debug.Print oName.Name & ": " & oName.RefersTo
Next oName

(Use oName.RefersToRange rather than RefersTo when you print, because the
former method will directly give you the Range object corresponding to the
defined name rather than just a simple string).

Hope this helps,
/MP

 

[Guest]

Hi - I'm really sorry to be such a pain - am learning this stuff as I go
along as I have kind of been thrown into this VBA project without any
training. I think I have been doing things in a rather muddled way but
seeing as my macros worked I didn't get worried - now I am though! Anyway.
So I executed your code and in the Immediate window the following came out :

My defined name ranges are...
'2048733'!COMMANDE: ='2048733'!$A$1:$E$100
COMMANDE: =#REF!$A$1:$E$100
'2048733'!CONDITIONNEMENT: ='2048733'!$A$901:$E$1100
CONDITIONNEMENT: =#REF!$A$401:$E$600
CONDITIONNEMENT_2: ='2048733'!$A$501:$E$700
CONDITIONNEMENT_3: ='2048733'!$A$701:$E$900
'2048733'!EMBALLAGE: ='2048733'!$A$1101:$E$1150
EMBALLAGE: =#REF!$A$801:$E$850
'2048733'!ETIQUETTES_DESTINATAIRE: ='2048733'!$A$101:$E$200
ETIQUETTES_DESTINATAIRE: =#REF!$A$101:$E$200
'2048733'!ETIQUETTES_PRODUIT: ='2048733'!$A$401:$E$500
ETIQUETTES_PRODUIT: =#REF!$A$201:$E$300
ETIQUETTES_PRODUIT_2: ='2048733'!$A$201:$E$300
ETIQUETTES_PRODUIT_3: ='2048733'!$A$301:$E$400
FEUILLE_COMPLEMENTAIRE: =#REF!$A$451:$E$500
'2048733'!VERIFICATION_DU_DOSSIER: ='2048733'!$A$1151:$E$1200
VERIFICATION_DU_DOSSIER: =#REF!$A$851:$E$900
'2048733'!Print_Area: ='2048733'!$A$1:$E$1200


What is bizarre is that it repeats each range twice but with one set
mentioning #REF! rather than the sheet name....Plus I had to define a
printarea in another part of my code which may confuse the whole range name
thing, no?

But going from the rest of it, it seems to bring out the right ranges. So I
tried out the following macro but it only turned up "COMMANDE" as a footer on
all of the pages so I obviously need to somehow put in the instruction "for
each page, mark page range name" but I just can't get my head round this
whole Dim, For, Each, Next stuff :

Sub tryfooter()
Dim oName As Name
With ActiveSheet.PageSetup
For Each oName In ActiveSheet.Names
.LeftFooter = oName.RefersToRange
Next oName
End With
End Sub

Once again am very grateful for your help.
rm81

"Mat P:son" a écrit :

 

[Guest]

Hi - I'm really sorry to be such a pain

No problem -- these forums are all about asking questions, right?

- am learning this stuff as I go
along as I have kind of been thrown into this VBA project without any
training.

We've all been there... )

I think I have been doing things in a rather muddled way but
seeing as my macros worked I didn't get worried - now I am though!
Hehehehe

Anyway.
So I executed your code and in the Immediate window the following came out :

My defined name ranges are...
'2048733'!COMMANDE: ='2048733'!$A$1:$E$100
COMMANDE: =#REF!$A$1:$E$100
'2048733'!CONDITIONNEMENT: ='2048733'!$A$901:$E$1100
CONDITIONNEMENT: =#REF!$A$401:$E$600
CONDITIONNEMENT_2: ='2048733'!$A$501:$E$700
CONDITIONNEMENT_3: ='2048733'!$A$701:$E$900
'2048733'!EMBALLAGE: ='2048733'!$A$1101:$E$1150
EMBALLAGE: =#REF!$A$801:$E$850
'2048733'!ETIQUETTES_DESTINATAIRE: ='2048733'!$A$101:$E$200
ETIQUETTES_DESTINATAIRE: =#REF!$A$101:$E$200
'2048733'!ETIQUETTES_PRODUIT: ='2048733'!$A$401:$E$500
ETIQUETTES_PRODUIT: =#REF!$A$201:$E$300
ETIQUETTES_PRODUIT_2: ='2048733'!$A$201:$E$300
ETIQUETTES_PRODUIT_3: ='2048733'!$A$301:$E$400
FEUILLE_COMPLEMENTAIRE: =#REF!$A$451:$E$500
'2048733'!VERIFICATION_DU_DOSSIER: ='2048733'!$A$1151:$E$1200
VERIFICATION_DU_DOSSIER: =#REF!$A$851:$E$900
'2048733'!Print_Area: ='2048733'!$A$1:$E$1200

What is bizarre is that it repeats each range twice but with one set
mentioning #REF! rather than the sheet name....

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.

Also note that it looks like some names are defined multiple times (e.g.,
"COMMANDE"). This is not quite true however: when we print out their fully
qualified names we can see that some are application-level names "COMMANDE:
=#REF!$A$1:$E$100" (this one's actually broken) and some are Sheet-level
names, (c.f., "'2048733'!COMMANDE", where "2048733" is the name of the sheet).

It's probably a good idea to keep them all as either Sheet-level names or
Application-level names (that is, not mixing them) because it'll be easier to
refer to them collectively. You can actually work with both types in the
Insert > Names > Define... dialogue: if you type in "MyRange" as the name
it'll become an Application-level name, while if you say for example
"Sheet1!MyRange" then you'll define a Sheet-level name. All the names you
generate automatically in the VBA code are Application-level names (but that
would be easy to change, should you wish to do so).

Plus I had to define a
printarea in another part of my code which may confuse the whole range name
thing, no?

Not really: you know what names you're using, and since "PrintArea" isn't
relevant to us we can always filter it out when we're going through the
collection of names.

But going from the rest of it, it seems to bring out the right ranges. So I
tried out the following macro but it only turned up "COMMANDE" as a footer on
all of the pages so I obviously need to somehow put in the instruction "for
each page, mark page range name" but I just can't get my head round this
whole Dim, For, Each, Next stuff :

Sub tryfooter()
Dim oName As Name
With ActiveSheet.PageSetup
For Each oName In ActiveSheet.Names
.LeftFooter = oName.RefersToRange
Next oName
End With
End Sub

That's a good first stab at the problem -- there are just a few minor issues:

1) oName is representing the defined name itself, so let's make sure it's
one of the names we want (i.e., let's use an If statement to grab only the
ones we're interested in)

2) The ActiveSheet does not contain all the names we've defined -- as I said
some of them reside on the application level, so you'll have to refer to the
application instead of the sheet (or move the defined names down to the sheet
level)

3) You can get hold of the name of each oName by refering to its Name
property (not to its RefersToRange property)

4) But we can (and should) use the RefersToRange property when printing,
because for each defined name we only want to print the range corresponding
to that particular name

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
)

=======================

' Main method to do all the printing
Private Sub PrintNames()

Dim oName As Name
Dim oSheet As Worksheet

' Iterate over all names defined in the active workbook
For Each oName In Application.Names
' TODO:
' I put them all in, so delete the ones you don't want
If _
InStr(1, oName.Name, "COMMANDE") > 0 Or _
InStr(1, oName.Name, "EMBALLAGE") > 0 Or _
InStr(1, oName.Name, "CONDITIONNEMENT") > 0 Or _
InStr(1, oName.Name, "FEUILLE_COMPLEMENTAIRE") > 0 Or _
InStr(1, oName.Name, "ETIQUETTES_PRODUIT") > 0 Or _
InStr(1, oName.Name, "VERIFICATION_DU_DOSSIER") > 0 Or _
InStr(1, oName.Name, "ETIQUETTES_DESTINATAIRE") > 0 _
Then
' We're interested in printing this one!
' TODO:
' If you don't want to use the sheet name in the footer,
' replace oName.Name with StripSheetName(oName.Name)
Set oSheet = Worksheets(GetSheetName(oName.RefersTo))
oSheet.PageSetup.LeftFooter = oName.Name
oName.RefersToRange.PrintOut Preview:=True
End If
Next oName

End Sub

' Called to extract the sheet name from a Name.ReferTo value
Private Function GetSheetName(sRefTo As String)
Dim iPosEq As Integer
Dim iPosEx As Integer

' The RefersTo values should be something like "=Sheet1!A1:B2"
' (the exact names will differ) and we want to extract "Sheet1".
iPosEq = InStr(1, sRefTo, "=")
iPosEx = InStr(1, sRefTo, "!")

If iPosEq > 0 And iPosEx > 0 And iPosEx > iPosEq Then
' Return the sheet name
GetSheetName = Mid$(sRefTo, iPosEq + 1, iPosEx - iPosEq - 1)
Else
' Ooops! We failed to find the sheet name! We'll crash and burn!
GetSheetName = ""
End If
End Function

' Get rid of the sheet name, might be useful for the Footer caption?
Private Function StripSheetName(sName As String)
Dim iPosEx As Integer

iPosEx = InStr(1, sName, "!")
If iPosEx > 0 Then
StripSheetName = Mid$(sName, iPosEx + 1)
Else
StripSheetName = sName
End If
End Function

=======================

 

[Guest]

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
)

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

 

[Guest]

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

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

 

[Guest]

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'

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?
Secondly - what do "iPosEq" and "iPosEx" refer to/mean/do? And why is
iPosEx 11?

Cheers,

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

 

[Guest]

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

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

heheheh )

Well, how about "later", then? Or "ta da"?
/MP

 

[Guest]

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 :

 

[Guest]

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.

Heheheh... Well, you're welcome -- I'm glad that things seem to work now.

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.

Ah, oh, yes, that's probably true... Bummer... /

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?

Most likely yes, I should think so. Which, in fact, I find a bit funny (no,
not funny-haha) since Excel usually never bothers to sort other collections
alphabetically (or at least not very often). Collections are, after all, just
bunches of "related stuff", whatever that's supposed to mean... )

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.

There are ugly solutions and nice solutions to the problem.

The quick'n'dirty one is to prepend for example "A_", "B_", "C_" etc to your
names, thus making sure that they show up in exactly the order you want. For
instance:

"A_COMMANDE" (and "A_COMMANDE_1", etc)
"B_ETIQUETTES_DESTINATAIRE" (etc)
"C_ETIQUETTES_PRODUIT" (etc)

Of course, you don't have to show the ugly little prefixes on your
print-outs; since we already have a function that extracts the label used in
the footer, let's modify it a wee bit:

StripSheetName = Mid$(sName, iPosEx + 1)

Becomes:

StripSheetName = Mid$(sName, iPosEx + 3)

(...Because we already know we have to get rid of those extra two characters
at the beginning of each of your defined names, right?)

And now: the nice way would perhaps be to keep the defined names as is, and
then create a new string array, in which the names appear in the preferred
order. Then we'd retrieve matching names from the Names collection repeatedly
until we've gone through them all. Sounds fun? Well, I leave as an exercise
for the over-performing reader... )

Once again sorry and thanks for all your help - you have been my saviour!

Well, the day I start posting questions on a French Language and Grammar
newsgroup I expect you to be there to help me out )

Right I'm off to have a nice cold beer. I'll drink to your health!

Enjoy! I think you're well worth it after the stressful Excel hacking... )

Have a good evening
rm81

You too, take care...
/MP

 

[Guest]

Hmmmm. I think I might just possibly go for the quick'n'dirty solution.
Thanks again!

Well, the day I start posting questions on a French Language and Grammar
newsgroup I expect you to be there to help me out )

Pas de problème mate!

Take care
rm81

 

[Guest]

Hi there...again!

Just a quick query - You know how I said it worked perfectly...er I don't
know why but it's doing weird things now. I put in your code (see below)
with the appropriate modifications you told me to do. The problem is that I
actually have several sheets all with named ranges. Now, I think you said
something about how ranges should be either all application level or all
sheet level - I think mine are all sheet level. But when I click on my
activesheet to print out with the footers and everything, it prints out the
activesheet PLUS the other worksheets!! I figured it must be the part where
you say

' Iterate over all names defined in the active workbook
For Each oName In Application.Names

I suppose that the code brings out all the sheets that all contain
"COMMANDE" etc etc. Is there anyway to specify that it should look for each
oName in the activesheet only? I hoped the following might work (obviously
too simple to be right) but realised that in the group "worksheet" there is
no object "activesheet".

For Each oName In Activesheet.Names

So, after lots of head scratching I decided to refer back to you. Hope you
don't mind!
Thankyou very much
rm81

=========================================
Private Sub CommandButton4_Click()
'Main method to do all the printing
Dim oName As Name
Dim oSheet As Worksheet

' Iterate over all names defined in the active workbook
For Each oName In Application.Names
' TODO:
' I put them all in, so delete the ones you don't want
If _
InStr(1, oName.Name, "COMMANDE") > 0 Or _
InStr(1, oName.Name, "ETIQUETTES_DESTINATAIRE") > 0 Or _
InStr(1, oName.Name, "ETIQUETTES_PRODUIT") > 0 Or _
InStr(1, oName.Name, "CONDITIONNEMENT") > 0 Or _
InStr(1, oName.Name, "EMBALLAGE") > 0 Or _
InStr(1, oName.Name, "VERIFICATION_DU_DOSSIER") > 0 _
Then
' We're interested in printing this one!

Set oSheet = Worksheets(GetSheetName(oName.RefersTo))
oSheet.PageSetup.LeftFooter = StripSheetName(oName.Name)
oName.RefersToRange.PrintOut Preview:=False
End If
Next oName

End Sub

' Called to extract the sheet name from a Name.ReferTo value
Private Function GetSheetName(sRefTo As String)
Dim iPosEq As Integer
Dim iPosEx As Integer

' The RefersTo values should be something like "=Sheet1!A1:B2"
' (the exact names will differ) and we want to extract "Sheet1".
iPosEq = InStr(1, sRefTo, "=")
iPosEx = InStr(1, sRefTo, "!")

If iPosEq > 0 And iPosEx > 0 And iPosEx > iPosEq Then
' Return the sheet name
GetSheetName = Mid$(sRefTo, iPosEq + 2, iPosEx - iPosEq - 3)
Else
' Ooops! We failed to find the sheet name! We'll crash and burn!
GetSheetName = ""
End If
End Function

' Get rid of the sheet name, might be useful for the Footer caption?
Private Function StripSheetName(sName As String)
Dim iPosEx As Integer

iPosEx = InStr(1, sName, "!")
If iPosEx > 0 Then
StripSheetName = Mid$(sName, iPosEx + 1)
Else
StripSheetName = sName
End If
End Function

===========================================


"rm81" a écrit :

 

[Tom Ogilvy]

if it is a sheet level name, that should loop through the names of the
Activesheet

For Each oName In Activesheet.Names

but to be sure

For Each oName In Activesheet.Names
if oName.RefersToRange.Parent.Name = Activesheet.Name then




end if
Next oName

assumes all your defined names refer to ranges.