Renamed ranges - PART 2
 

Hi there,

I think my reply to my earlier question has got pushed quite far down and as
I still have another question regarding this issue so am reposting it. For
first part of issue please see further below for history and reply from Mat
P:son.

So, assuming you have read Part 1, I shall continue : my total number of
pages being variable (see code for this in Part 1 below), I would like to
know if it is possible to print the range names on each page as a footer. So
for example "COMMANDE" on pages 1-2, "ETIQUETTES DESTINATAIRE" on pages 3-4
and then "CONDITIONNEMENT_1" etc. I had previously asked the same kind of
question but hadn't specified that the number of pages would be variable and
so the person gave me a solution using specific page numbers :

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

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

Thankyou!
rm8
------------------------------------------------------------------------------------------------
RENAMED RANGES - PART 1

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

Renamed ranges - PART 2
 

Please see my reply to your previous posting... It's all in there :o)

Cheers,
/MP

"rm81" wrote:

Hi there,

I think my reply to my earlier question has got pushed quite far down and as
I still have another question regarding this issue so am reposting it. For
first part of issue please see further below for history and reply from Mat
P:son.

So, assuming you have read Part 1, I shall continue : my total number of
pages being variable (see code for this in Part 1 below), I would like to
know if it is possible to print the range names on each page as a footer. So
for example "COMMANDE" on pages 1-2, "ETIQUETTES DESTINATAIRE" on pages 3-4
and then "CONDITIONNEMENT_1" etc. I had previously asked the same kind of
question but hadn't specified that the number of pages would be variable and
so the person gave me a solution using specific page numbers :

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

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

Thankyou!
rm81
------------------------------------------------------------------------------------------------
RENAMED RANGES - PART 1

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