Range names in macros - DA
 

One of the things I don't like about macros is that, if you insert rows, it
will mess up macros programmed to go to specific locations. As my massive
files evolve, I become paranoid, not to do anything that could move things
around (and I'm not organized enough to keep a log of such cells). This
handcuffs me.

I understand that, if you give a cell(s) a range name, then you can use that
instead and then the macro will adjust if the cell gets moved. Can someone
write me the equivalent of the following code if I name the first cell "Joe"
and the block of cells "Jane"?

Application.Goto Reference:="R16C4"
ActiveCell.FormulaR1C1 = "0"
Selection.Copy
Range("D16:D18").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!
Dean

Range names in macros - DA
 

Dim rng as Range
With Range("Jane")
set rng = .Parent.Range(.Cells,.End(xltoRight))
End With
Range("Joe").copy
rng Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--
Regards,
Tom Ogilvy



"Dean" wrote:

One of the things I don't like about macros is that, if you insert rows, it
will mess up macros programmed to go to specific locations. As my massive
files evolve, I become paranoid, not to do anything that could move things
around (and I'm not organized enough to keep a log of such cells). This
handcuffs me.

I understand that, if you give a cell(s) a range name, then you can use that
instead and then the macro will adjust if the cell gets moved. Can someone
write me the equivalent of the following code if I name the first cell "Joe"
and the block of cells "Jane"?

Application.Goto Reference:="R16C4"
ActiveCell.FormulaR1C1 = "0"
Selection.Copy
Range("D16:D18").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!
Dean

Range names in macros - DA
 

thanks tom

"Tom Ogilvy" wrote in message
...

Dim rng as Range
With Range("Jane")
set rng = .Parent.Range(.Cells,.End(xltoRight))
End With
Range("Joe").copy
rng Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--
Regards,
Tom Ogilvy



"Dean" wrote:

One of the things I don't like about macros is that, if you insert rows,
it
will mess up macros programmed to go to specific locations. As my
massive
files evolve, I become paranoid, not to do anything that could move
things
around (and I'm not organized enough to keep a log of such cells). This
handcuffs me.

I understand that, if you give a cell(s) a range name, then you can use
that
instead and then the macro will adjust if the cell gets moved. Can
someone
write me the equivalent of the following code if I name the first cell
"Joe"
and the block of cells "Jane"?

Application.Goto Reference:="R16C4"
ActiveCell.FormulaR1C1 = "0"
Selection.Copy
Range("D16:D18").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!
Dean

Range names in macros - DA
 

By the way, I assume there is a way to require a password to run each macro
somehow, but it would be better if they could simply be disabled only if and
when I choose to do so, say just before I mail it to someone. I'd rather
not have to supply a password every time that I run them.

Thanks!
Dean


"Dean" wrote in message
...
thanks tom

"Tom Ogilvy" wrote in message
...

Dim rng as Range
With Range("Jane")
set rng = .Parent.Range(.Cells,.End(xltoRight))
End With
Range("Joe").copy
rng Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--
Regards,
Tom Ogilvy



"Dean" wrote:

One of the things I don't like about macros is that, if you insert rows,
it
will mess up macros programmed to go to specific locations. As my
massive
files evolve, I become paranoid, not to do anything that could move
things
around (and I'm not organized enough to keep a log of such cells). This
handcuffs me.

I understand that, if you give a cell(s) a range name, then you can use
that
instead and then the macro will adjust if the cell gets moved. Can
someone
write me the equivalent of the following code if I name the first cell
"Joe"
and the block of cells "Jane"?

Application.Goto Reference:="R16C4"
ActiveCell.FormulaR1C1 = "0"
Selection.Copy
Range("D16:D18").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!
Dean

Range names in macros - DA
 

sorry, I posted this to the wrong thread. Ignore it here.

"Dean" wrote in message
...
By the way, I assume there is a way to require a password to run each
macro somehow, but it would be better if they could simply be disabled
only if and when I choose to do so, say just before I mail it to someone.
I'd rather not have to supply a password every time that I run them.

Thanks!
Dean


"Dean" wrote in message
...
thanks tom

"Tom Ogilvy" wrote in message
...

Dim rng as Range
With Range("Jane")
set rng = .Parent.Range(.Cells,.End(xltoRight))
End With
Range("Joe").copy
rng Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--
Regards,
Tom Ogilvy



"Dean" wrote:

One of the things I don't like about macros is that, if you insert
rows, it
will mess up macros programmed to go to specific locations. As my
massive
files evolve, I become paranoid, not to do anything that could move
things
around (and I'm not organized enough to keep a log of such cells).
This
handcuffs me.

I understand that, if you give a cell(s) a range name, then you can use
that
instead and then the macro will adjust if the cell gets moved. Can
someone
write me the equivalent of the following code if I name the first cell
"Joe"
and the block of cells "Jane"?

Application.Goto Reference:="R16C4"
ActiveCell.FormulaR1C1 = "0"
Selection.Copy
Range("D16:D18").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!
Dean