Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names -VBA
What is wrong with the responses given yesterday?
-- HTH RP (remove nothere from the email address if mailing direct) "JOhnUK" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names -VBA
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names -VBA
John,
Check out Google http://tinyurl.com/dqy8g -- HTH RP (remove nothere from the email address if mailing direct) "JohnUK" wrote in message ... 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
range names | Excel Discussion (Misc queries) |