Copy named range based on variable
Is it possible to copy a named range based on a variable?
I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ...... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). -- Ian -- |
Copy named range based on variable
If you have a string that names a range, just use Range to get a real range from the string. E.g., sMTSVer = "some string" Set R = Range(sMTSVer) R.Copy destiantion:=Range("A5") Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 10 Dec 2009 19:37:29 -0000, "IanC" wrote: Is it possible to copy a named range based on a variable? I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ..... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). |
Copy named range based on variable
Thanks Chip. It wasn't quite the way I'd intended, but with a bit of
recoding it's working well. -- Ian -- "Chip Pearson" wrote in message ... If you have a string that names a range, just use Range to get a real range from the string. E.g., sMTSVer = "some string" Set R = Range(sMTSVer) R.Copy destiantion:=Range("A5") Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 10 Dec 2009 19:37:29 -0000, "IanC" wrote: Is it possible to copy a named range based on a variable? I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ..... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). |
All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com