LinkBack URL
About LinkBacksHi 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
Register To Reply
Bookmarks