Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range changes values
Excel 2003 SR2
I'm working through some VBA and save often to other worksheets. I have several named ranges (Rows1_5000, 5001-10000, etc). The range is from $A$1 to $K$5000. But I notice that ranges sometimes change to H or C in place of K. I have the $ in front of the row and column - I thought that would keep it defined as I wanted. How come it changes? What can I do to keep it the way I first defined it? It is very time consuming to continously check and change it. TIA, CaroleO |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range changes values
Carole O,
Have you tried Row and Column reference in your VBA program? $A$1 is R1C1 $K$5000 is R5000C11 VBA might work better with this reference. HTH :-) "Carole O" wrote: Excel 2003 SR2 I'm working through some VBA and save often to other worksheets. I have several named ranges (Rows1_5000, 5001-10000, etc). The range is from $A$1 to $K$5000. But I notice that ranges sometimes change to H or C in place of K. I have the $ in front of the row and column - I thought that would keep it defined as I wanted. How come it changes? What can I do to keep it the way I first defined it? It is very time consuming to continously check and change it. TIA, CaroleO |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range changes values
Based on the error I got when I entered R1C1:R5000C11 in the Named Range, I'm
guessing this has to replace the Range reference in the VBA Code. This is what I have: Range("A1").Select Application.Goto Reference:="Rows1_5000" Selection.Copy Sheets("5000").Select ActiveSheet.Paste How should the second line look? "FloMM2" wrote: Carole O, Have you tried Row and Column reference in your VBA program? $A$1 is R1C1 $K$5000 is R5000C11 VBA might work better with this reference. HTH :-) "Carole O" wrote: Excel 2003 SR2 I'm working through some VBA and save often to other worksheets. I have several named ranges (Rows1_5000, 5001-10000, etc). The range is from $A$1 to $K$5000. But I notice that ranges sometimes change to H or C in place of K. I have the $ in front of the row and column - I thought that would keep it defined as I wanted. How come it changes? What can I do to keep it the way I first defined it? It is very time consuming to continously check and change it. TIA, CaroleO |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range changes values
CaroleO,
This is what it should look like: Range("A1:K5000").Select Selection.Copy Sheets("Sheet10").Select Range("A1").Select ActiveSheet.Paste End Sub Range("A1:K5000") or Range("$A$1:$K$5000") is the choice to copy. Sheets("Sheet10") is the destination worksheet. Range("A1") is the start position of the copy. HTH Good Luck, :-) "CaroleO" wrote: Based on the error I got when I entered R1C1:R5000C11 in the Named Range, I'm guessing this has to replace the Range reference in the VBA Code. This is what I have: Range("A1").Select Application.Goto Reference:="Rows1_5000" Selection.Copy Sheets("5000").Select ActiveSheet.Paste How should the second line look? "FloMM2" wrote: Carole O, Have you tried Row and Column reference in your VBA program? $A$1 is R1C1 $K$5000 is R5000C11 VBA might work better with this reference. HTH :-) "Carole O" wrote: Excel 2003 SR2 I'm working through some VBA and save often to other worksheets. I have several named ranges (Rows1_5000, 5001-10000, etc). The range is from $A$1 to $K$5000. But I notice that ranges sometimes change to H or C in place of K. I have the $ in front of the row and column - I thought that would keep it defined as I wanted. How come it changes? What can I do to keep it the way I first defined it? It is very time consuming to continously check and change it. TIA, CaroleO |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range changes values
Carole
you could just use: Range("Rows_5000").Copy Sheets("5000").Range("A1") Regards Trevor "CaroleO" wrote in message ... Based on the error I got when I entered R1C1:R5000C11 in the Named Range, I'm guessing this has to replace the Range reference in the VBA Code. This is what I have: Range("A1").Select Application.Goto Reference:="Rows1_5000" Selection.Copy Sheets("5000").Select ActiveSheet.Paste How should the second line look? "FloMM2" wrote: Carole O, Have you tried Row and Column reference in your VBA program? $A$1 is R1C1 $K$5000 is R5000C11 VBA might work better with this reference. HTH :-) "Carole O" wrote: Excel 2003 SR2 I'm working through some VBA and save often to other worksheets. I have several named ranges (Rows1_5000, 5001-10000, etc). The range is from $A$1 to $K$5000. But I notice that ranges sometimes change to H or C in place of K. I have the $ in front of the row and column - I thought that would keep it defined as I wanted. How come it changes? What can I do to keep it the way I first defined it? It is very time consuming to continously check and change it. TIA, CaroleO |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
formula for named cell/range using cell values | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) |