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

"rm81" wrote:

:)
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 :



"rm81" wrote:

Genius!!

Thanks! (...from a humble genius :o)

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

I'm happy that it worked

Best regards,
rm81


"Mat P:son" a écrit :

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

"rm81" wrote:

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("C6:D6").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