Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Named Ranges with Charts: Part 2 (!) | Excel Worksheet Functions | |||
Renamed invalid sheet name | Excel Discussion (Misc queries) | |||
Trying to open file, may have renamed it | Excel Discussion (Misc queries) | |||
Renamed File | Excel Programming | |||
Do not want sheet renamed | Excel Programming |