| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming a range
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a range
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a range
Genius!! Thanks ever so much for your help - it works like a dream.
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a range
"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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a range
:)
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a range
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| renaming a named range | Excel Discussion (Misc queries) | |||
| Putting Sheet Names in a range and renaming it? | Excel Worksheet Functions | |||
| renaming | Excel Programming | |||
| Renaming | Excel Programming | |||
| Add-In / Tool / VBA Code for Renaming Range Names | Excel Programming | |||
Hybrid Mode
