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

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

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

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