Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB for multiple cell names
Hello,
I have two sets (paired) of 29 named cells (58 named cells total). In each pair, I want to copy the contents from cell and paste it to the other. Typically I'd think of a loop here and feed in each pair of cell names, but I don't know how to do this in Excel VB. Here's my current code for one pair. Application.Goto Reference:="ModemStandardRent" Selection.Copy Application.Goto Reference:="BuildAModemStandardRent" ActiveSheet.Paste Application.CutCopyMode = False Does anyone know of a way to handle this, other than pasting the above code 29 times and inserting each pair set of names? Thank you in advance. Carolyn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB for multiple cell names
Is there a pattern in the names that can be detected?
otherwise Dim v as Variant, v1 as Variant, i as long v = Array("Name1", "Name2", "Name3", "ModemStandardRent", "Name4") v1 = Array("Name1A", "Name2A", "House", "BuildAModemStandardRent", "Yard") for i = lbound(v) to ubound(v) range(v(i)).Copy Destination:=Range(v1(i)) Next -- Regards, Tom Ogilvy "Carolyn" wrote in message ... Hello, I have two sets (paired) of 29 named cells (58 named cells total). In each pair, I want to copy the contents from cell and paste it to the other. Typically I'd think of a loop here and feed in each pair of cell names, but I don't know how to do this in Excel VB. Here's my current code for one pair. Application.Goto Reference:="ModemStandardRent" Selection.Copy Application.Goto Reference:="BuildAModemStandardRent" ActiveSheet.Paste Application.CutCopyMode = False Does anyone know of a way to handle this, other than pasting the above code 29 times and inserting each pair set of names? Thank you in advance. Carolyn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB for multiple cell names
Tom,
Thank you very much. This works beautifully. Incidently, no, currently the names are not in an "easy" pattern, though there is a pattern. That said, I am the one who created the names, so if there is a way to pattern them that will save me some programming, I would love to know. This discussion group forum is proving extremely valuable to me. Thank you Tom for your time and participation! Carolyn "Tom Ogilvy" wrote: Is there a pattern in the names that can be detected? otherwise Dim v as Variant, v1 as Variant, i as long v = Array("Name1", "Name2", "Name3", "ModemStandardRent", "Name4") v1 = Array("Name1A", "Name2A", "House", "BuildAModemStandardRent", "Yard") for i = lbound(v) to ubound(v) range(v(i)).Copy Destination:=Range(v1(i)) Next -- Regards, Tom Ogilvy "Carolyn" wrote in message ... Hello, I have two sets (paired) of 29 named cells (58 named cells total). In each pair, I want to copy the contents from cell and paste it to the other. Typically I'd think of a loop here and feed in each pair of cell names, but I don't know how to do this in Excel VB. Here's my current code for one pair. Application.Goto Reference:="ModemStandardRent" Selection.Copy Application.Goto Reference:="BuildAModemStandardRent" ActiveSheet.Paste Application.CutCopyMode = False Does anyone know of a way to handle this, other than pasting the above code 29 times and inserting each pair set of names? Thank you in advance. Carolyn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB for multiple cell names
if they were
name1 name1a and the pairing was just the root name plus an A on the end and the names all start with n (so you can have other names that don't for example) Dim nm as Name, s as String for each nm in Thisworkbook.Names s = lcase(nm.name) if left(s,1) = "n" and right(s,1) < "a" then nm.ReferstoRange.Copy Destination:=Range(s & "a") Next so that is just an example pattern, but any pattern you can "solve" for. -- Regards, Tom Ogilvy "Carolyn" wrote in message ... Tom, Thank you very much. This works beautifully. Incidently, no, currently the names are not in an "easy" pattern, though there is a pattern. That said, I am the one who created the names, so if there is a way to pattern them that will save me some programming, I would love to know. This discussion group forum is proving extremely valuable to me. Thank you Tom for your time and participation! Carolyn "Tom Ogilvy" wrote: Is there a pattern in the names that can be detected? otherwise Dim v as Variant, v1 as Variant, i as long v = Array("Name1", "Name2", "Name3", "ModemStandardRent", "Name4") v1 = Array("Name1A", "Name2A", "House", "BuildAModemStandardRent", "Yard") for i = lbound(v) to ubound(v) range(v(i)).Copy Destination:=Range(v1(i)) Next -- Regards, Tom Ogilvy "Carolyn" wrote in message ... Hello, I have two sets (paired) of 29 named cells (58 named cells total). In each pair, I want to copy the contents from cell and paste it to the other. Typically I'd think of a loop here and feed in each pair of cell names, but I don't know how to do this in Excel VB. Here's my current code for one pair. Application.Goto Reference:="ModemStandardRent" Selection.Copy Application.Goto Reference:="BuildAModemStandardRent" ActiveSheet.Paste Application.CutCopyMode = False Does anyone know of a way to handle this, other than pasting the above code 29 times and inserting each pair set of names? Thank you in advance. Carolyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell names across multiple worksheets | Excel Discussion (Misc queries) | |||
How do i use the same cell name accross multiple tab names in a wo | Excel Discussion (Misc queries) | |||
Formula - Capture Multiple names into One cell | New Users to Excel | |||
How do I sort multiple names within one cell? | Excel Worksheet Functions | |||
apply cell names to formulas in multiple worksheets | Excel Worksheet Functions |