modify macro - Help please!!

Hi everyone,

I'm VBA dumb so please bear with me. I have a macro that inserts a row at the selection and then I want it to copy the cell below the selection and paste into the new row's cell. Here's what's in the macro now:

Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"), Type:=xlFillCopy
Range("D2223").Select

When I run the macro, a row is inserted above the cursor's location (no matter where it's placed) but the AutoFill is always to D23. My question is how do I get the code to autofill the new blank cell?

TIA

rpw

try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub

--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,

I'm VBA dumb so please bear with me. I have a macro that inserts a row at
the selection and then I want it to copy the cell below the selection and
paste into the new row's cell. Here's what's in the macro now:

Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"), Type:=xlFillCopy
Range("D2223").Select

When I run the macro, a row is inserted above the cursor's location (no
matter where it's placed) but the AutoFill is always to D23. My question is
how do I get the code to autofill the new blank cell?

TIA

rpw

Hi Don,

Thanks for helping. Here's what I've got and it's not working (it gets a Compile error; Invalid qualifier):

Sub InsertRow()
ActiveCell.Row.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub

So I tried this and I get a row inserted, and the contents of the cell above are copied to the new cell:

Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Select
ActiveCell = ActiveCell.Offset(-1)
End Sub

So now that step now works fine. Thanks for the activecell.offset thing! I'm going to do a little more research/testing to see if I can't expand the macro to do more. If not, I'll be back with more questions.
Thanks!

----- Don Guillett wrote: -----

try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub

--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,
I'm VBA dumb so please bear with me. I have a macro that inserts a row at
the selection and then I want it to copy the cell below the selection and
paste into the new row's cell. Here's what's in the macro now:
Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"), Type:=xlFillCopy
Range("D2223").Select
When I run the macro, a row is inserted above the cursor's location (no
matter where it's placed) but the AutoFill is always to D23. My question is
how do I get the code to autofill the new blank cell?
TIA
rpw

Slight variation, combining Don's answer with what you've got:

ActiveCell.EntireRow.Insert
ActiveCell = ActiveCell.Offset(-1)

Regards

Trevor


"rpw" wrote in message
...
Hi Don,

Thanks for helping. Here's what I've got and it's not working (it gets a
Compile error; Invalid qualifier):

Sub InsertRow()
ActiveCell.Row.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub

So I tried this and I get a row inserted, and the contents of the cell
above are copied to the new cell:

Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Select
ActiveCell = ActiveCell.Offset(-1)
End Sub

So now that step now works fine. Thanks for the activecell.offset thing!
I'm going to do a little more research/testing to see if I can't expand the
macro to do more. If not, I'll be back with more questions.
Thanks!

----- Don Guillett wrote: -----

try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub

--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,
I'm VBA dumb so please bear with me. I have a macro that inserts
a row at
the selection and then I want it to copy the cell below the selection
and
paste into the new row's cell. Here's what's in the macro now:
Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"),
Type:=xlFillCopy
Range("D2223").Select
When I run the macro, a row is inserted above the cursor's
location (no
matter where it's placed) but the AutoFill is always to D23. My
question is
how do I get the code to autofill the new blank cell?
TIA
rpw

Hey, thanks! To both of you! (Don and Trevor) I really appreciate your help.

ActiveCell and Offset keywords pointed me in the right direction. From there with VBA's help and the automatic drop-down selections, I came up with something that works for my problem. I only wanted to copy a certain range and paste it into a new row, and then clear out some individual cells (that do not have formulas or conditional formatting). Here's what I've come up with that is now working:

Sub InsertRow()
Selection.EntireRow.Insert
Range(ActiveCell.Offset(-1), ActiveCell.Offset(-1, 11)).Copy
Range(ActiveCell, ActiveCell.Offset(, 11)).PasteSpecial xlPasteAll
ActiveCell.ClearContents
ActiveCell.Offset(, 11).ClearContents
End Sub

Any comments, evaluations, or alternatives will be appreciated.

TIA
rpw

----- Trevor Shuttleworth wrote: -----

Slight variation, combining Don's answer with what you've got:

ActiveCell.EntireRow.Insert
ActiveCell = ActiveCell.Offset(-1)

Regards

Trevor


"rpw" wrote in message
...
Hi Don,
Thanks for helping. Here's what I've got and it's not working (it gets a
Compile error; Invalid qualifier):
Sub InsertRow()
ActiveCell.Row.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
So I tried this and I get a row inserted, and the contents of the cell
above are copied to the new cell:
Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Select
ActiveCell = ActiveCell.Offset(-1)
End Sub
So now that step now works fine. Thanks for the activecell.offset thing!
I'm going to do a little more research/testing to see if I can't expand the
macro to do more. If not, I'll be back with more questions.
Thanks!
----- Don Guillett wrote: -----
try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,
I'm VBA dumb so please bear with me. I have a macro that inserts
a row at
the selection and then I want it to copy the cell below the selection
and
paste into the new row's cell. Here's what's in the macro now:
Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"),
Type:=xlFillCopy
Range("D2223").Select
When I run the macro, a row is inserted above the cursor's
location (no
matter where it's placed) but the AutoFill is always to D23. My
question is
how do I get the code to autofill the new blank cell?
TIA
rpw

Hey, when you are ahead - STOP. Or, as I used to tell the salesmen that
worked for me, "When the sale is made, quit selling or they might stop
buying"

--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hey, thanks! To both of you! (Don and Trevor) I really appreciate your
help.

ActiveCell and Offset keywords pointed me in the right direction. From
there with VBA's help and the automatic drop-down selections, I came up with
something that works for my problem. I only wanted to copy a certain range
and paste it into a new row, and then clear out some individual cells (that
do not have formulas or conditional formatting). Here's what I've come up
with that is now working:

Sub InsertRow()
Selection.EntireRow.Insert
Range(ActiveCell.Offset(-1), ActiveCell.Offset(-1, 11)).Copy
Range(ActiveCell, ActiveCell.Offset(, 11)).PasteSpecial xlPasteAll
ActiveCell.ClearContents
ActiveCell.Offset(, 11).ClearContents
End Sub

Any comments, evaluations, or alternatives will be appreciated.

TIA
rpw

----- Trevor Shuttleworth wrote: -----

Slight variation, combining Don's answer with what you've got:

ActiveCell.EntireRow.Insert
ActiveCell = ActiveCell.Offset(-1)

Regards

Trevor


"rpw" wrote in message
...
Hi Don,
Thanks for helping. Here's what I've got and it's not working (it
gets a
Compile error; Invalid qualifier):
Sub InsertRow()
ActiveCell.Row.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
So I tried this and I get a row inserted, and the contents of the
cell
above are copied to the new cell:
Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Select
ActiveCell = ActiveCell.Offset(-1)
End Sub
So now that step now works fine. Thanks for the activecell.offset
thing!
I'm going to do a little more research/testing to see if I can't
expand the
macro to do more. If not, I'll be back with more questions.
Thanks!
----- Don Guillett wrote: -----
try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,
I'm VBA dumb so please bear with me. I have a macro that inserts
a row at
the selection and then I want it to copy the cell below the
selection
and
paste into the new row's cell. Here's what's in the macro
now:
Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"),
Type:=xlFillCopy
Range("D2223").Select
When I run the macro, a row is inserted above the cursor's
location (no
matter where it's placed) but the AutoFill is always to D23.
My
question is
how do I get the code to autofill the new blank cell?
TIA
rpw

HAHA! You're right! But I did do one more little thing that made it more convenient - I added ActiveCell.Select to the end so that the cell I want to type new stuff into is already selected. And that'll be it on this little macro! 'Twas a great little learning experience!

rpw

----- Don Guillett wrote: -----

Hey, when you are ahead - STOP. Or, as I used to tell the salesmen that
worked for me, "When the sale is made, quit selling or they might stop
buying"

--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hey, thanks! To both of you! (Don and Trevor) I really appreciate your
help.
ActiveCell and Offset keywords pointed me in the right direction. From
there with VBA's help and the automatic drop-down selections, I came up with
something that works for my problem. I only wanted to copy a certain range
and paste it into a new row, and then clear out some individual cells (that
do not have formulas or conditional formatting). Here's what I've come up
with that is now working:
Sub InsertRow()
Selection.EntireRow.Insert
Range(ActiveCell.Offset(-1), ActiveCell.Offset(-1, 11)).Copy
Range(ActiveCell, ActiveCell.Offset(, 11)).PasteSpecial xlPasteAll
ActiveCell.ClearContents
ActiveCell.Offset(, 11).ClearContents
End Sub
Any comments, evaluations, or alternatives will be appreciated.
TIA
rpw
----- Trevor Shuttleworth wrote: -----
Slight variation, combining Don's answer with what you've got:
ActiveCell.EntireRow.Insert
ActiveCell = ActiveCell.Offset(-1)
Regards
Trevor
"rpw" wrote in message
...
Hi Don,
Thanks for helping. Here's what I've got and it's not working (it
gets a
Compile error; Invalid qualifier):
Sub InsertRow()
ActiveCell.Row.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
So I tried this and I get a row inserted, and the contents of the
cell
above are copied to the new cell:
Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Select
ActiveCell = ActiveCell.Offset(-1)
End Sub
So now that step now works fine. Thanks for the activecell.offset
thing!
I'm going to do a little more research/testing to see if I can't
expand the
macro to do more. If not, I'll be back with more questions.
Thanks!
----- Don Guillett wrote: -----
try
Sub copyrowbelow()
ActiveCell.Rows.Insert
ActiveCell = ActiveCell.Offset(1)
End Sub
--
Don Guillett
SalesAid Software

"rpw" wrote in message
...
Hi everyone,
I'm VBA dumb so please bear with me. I have a macro that inserts
a row at
the selection and then I want it to copy the cell below the
selection
and
paste into the new row's cell. Here's what's in the macro
now:
Selection.EntireRow.Insert
Range("D23").Select
Selection.AutoFill Destination:=Range("D2223"),
Type:=xlFillCopy
Range("D2223").Select
When I run the macro, a row is inserted above the cursor's
location (no
matter where it's placed) but the AutoFill is always to D23.
My
question is
how do I get the code to autofill the new blank cell?
TIA
rpw