Range names -VBA

Hi, I need a short code that pick up Range names from a
given number in a cell, and paste elsewhere. The code
below works, but I have in the region of a thousand named
ranges, and as you can imagine, if I kept to the way the
code below is written, it would be extremely long, and
time consuming.

If ActiveCell = "4126" Then
Application.Goto Reference:="A_4126"
Selection.Copy
Sheets("Main Page").Select
Range("B10").Select
ActiveSheet.Paste
Application.Goto Reference:="B_4126"
Selection.Copy
Sheets("Main Page").Select
Range("B29").Select
ActiveSheet.Paste
Range("A1").Select
End If
If ActiveCell = "4128" Then
Application.Goto Reference:="A_4128"
Selection.Copy
Sheets("Main Page").Select
Range("B10").Select
ActiveSheet.Paste
Application.Goto Reference:="B_4128"
Selection.Copy
Sheets("Main Page").Select
Range("B29").Select
ActiveSheet.Paste
Range("A1").Select
End If

And so on, and so on.
Any help greatly appreciated
John

What is wrong with the responses given yesterday?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JOhnUK" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need a short code that pick up Range names from a
> given number in a cell, and paste elsewhere. The code
> below works, but I have in the region of a thousand named
> ranges, and as you can imagine, if I kept to the way the
> code below is written, it would be extremely long, and
> time consuming.
>
> If ActiveCell = "4126" Then
> Application.Goto Reference:="A_4126"
> Selection.Copy
> Sheets("Main Page").Select
> Range("B10").Select
> ActiveSheet.Paste
> Application.Goto Reference:="B_4126"
> Selection.Copy
> Sheets("Main Page").Select
> Range("B29").Select
> ActiveSheet.Paste
> Range("A1").Select
> End If
> If ActiveCell = "4128" Then
> Application.Goto Reference:="A_4128"
> Selection.Copy
> Sheets("Main Page").Select
> Range("B10").Select
> ActiveSheet.Paste
> Application.Goto Reference:="B_4128"
> Selection.Copy
> Sheets("Main Page").Select
> Range("B29").Select
> ActiveSheet.Paste
> Range("A1").Select
> End If
>
> And so on, and so on.
> Any help greatly appreciated
> John
>

Sorry Bob, But I can't access yesterdays post, so I had
no idea that it was even answered
John
>-----Original Message-----
>What is wrong with the responses given yesterday?
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"JOhnUK" <[email protected]> wrote in
message
>news:[email protected]...
>> Hi, I need a short code that pick up Range names from a
>> given number in a cell, and paste elsewhere. The code
>> below works, but I have in the region of a thousand
named
>> ranges, and as you can imagine, if I kept to the way
the
>> code below is written, it would be extremely long, and
>> time consuming.
>>
>> If ActiveCell = "4126" Then
>> Application.Goto Reference:="A_4126"
>> Selection.Copy
>> Sheets("Main Page").Select
>> Range("B10").Select
>> ActiveSheet.Paste
>> Application.Goto Reference:="B_4126"
>> Selection.Copy
>> Sheets("Main Page").Select
>> Range("B29").Select
>> ActiveSheet.Paste
>> Range("A1").Select
>> End If
>> If ActiveCell = "4128" Then
>> Application.Goto Reference:="A_4128"
>> Selection.Copy
>> Sheets("Main Page").Select
>> Range("B10").Select
>> ActiveSheet.Paste
>> Application.Goto Reference:="B_4128"
>> Selection.Copy
>> Sheets("Main Page").Select
>> Range("B29").Select
>> ActiveSheet.Paste
>> Range("A1").Select
>> End If
>>
>> And so on, and so on.
>> Any help greatly appreciated
>> John
>>
>
>
>.
>

John,

Check out Google

http://tinyurl.com/dqy8g

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" <[email protected]> wrote in message
news:[email protected]...
> Sorry Bob, But I can't access yesterdays post, so I had
> no idea that it was even answered
> John
> >-----Original Message-----
> >What is wrong with the responses given yesterday?
> >
> >--
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"JOhnUK" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> Hi, I need a short code that pick up Range names from a
> >> given number in a cell, and paste elsewhere. The code
> >> below works, but I have in the region of a thousand
> named
> >> ranges, and as you can imagine, if I kept to the way
> the
> >> code below is written, it would be extremely long, and
> >> time consuming.
> >>
> >> If ActiveCell = "4126" Then
> >> Application.Goto Reference:="A_4126"
> >> Selection.Copy
> >> Sheets("Main Page").Select
> >> Range("B10").Select
> >> ActiveSheet.Paste
> >> Application.Goto Reference:="B_4126"
> >> Selection.Copy
> >> Sheets("Main Page").Select
> >> Range("B29").Select
> >> ActiveSheet.Paste
> >> Range("A1").Select
> >> End If
> >> If ActiveCell = "4128" Then
> >> Application.Goto Reference:="A_4128"
> >> Selection.Copy
> >> Sheets("Main Page").Select
> >> Range("B10").Select
> >> ActiveSheet.Paste
> >> Application.Goto Reference:="B_4128"
> >> Selection.Copy
> >> Sheets("Main Page").Select
> >> Range("B29").Select
> >> ActiveSheet.Paste
> >> Range("A1").Select
> >> End If
> >>
> >> And so on, and so on.
> >> Any help greatly appreciated
> >> John
> >>
> >
> >
> >.
> >