Named range changes values

C

Carole O

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
 
F

FloMM2

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
:)
 
C

CaroleO

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?
 
F

FloMM2

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,
:)
 
T

Trevor Shuttleworth

Carole

you could just use:

Range("Rows_5000").Copy Sheets("5000").Range("A1")

Regards

Trevor
 
Top