modify macro - Help please!!

[rpw]

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.Inser
Range("D23").Selec
Selection.AutoFill Destination:=Range("D2223"), Type:=xlFillCop
Range("D2223").Selec

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

TI

rpw

 

[Don Guillett]

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

--
Don Guillett
SalesAid Software
(e-mail address removed)

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?

 

[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.Inser
ActiveCell = ActiveCell.Offset(1
End Su

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.Inser
ActiveCell.Selec
ActiveCell = ActiveCell.Offset(-1
End Su

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

tr
Sub copyrowbelow(
ActiveCell.Rows.Inser
ActiveCell = ActiveCell.Offset(1
End Su

--
Don Guillet
SalesAid Softwar
(e-mail address removed)

Hi everyone

the selection and then I want it to copy the cell below the selection an
paste into the new row's cell. Here's what's in the macro now

Range("D23").Selec
Selection.AutoFill Destination:=Range("D2223"), Type:=xlFillCop
Range("D2223").Selec

matter where it's placed) but the AutoFill is always to D23. My question i
how do I get the code to autofill the new blank cell

 

[Trevor Shuttleworth]

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

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

Regards

Trevor

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.

 

[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.Inser
Range(ActiveCell.Offset(-1), ActiveCell.Offset(-1, 11)).Cop
Range(ActiveCell, ActiveCell.Offset(, 11)).PasteSpecial xlPasteAl
ActiveCell.ClearContent
ActiveCell.Offset(, 11).ClearContent
End Su

Any comments, evaluations, or alternatives will be appreciated

TI
rp

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

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

ActiveCell.EntireRow.Inser
ActiveCell = ActiveCell.Offset(-1

Regard

Trevo

Hi Don
ActiveCell.Row.Inser
ActiveCell = ActiveCell.Offset(1
End Su
Selection.EntireRow.Inser
ActiveCell.Selec
ActiveCell = ActiveCell.Offset(-1
End Su

I'm going to do a little more research/testing to see if I can't expand th
macro to do more. If not, I'll be back with more questions

 

[Don Guillett]

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
(e-mail address removed)

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:

 

[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

rp

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

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

--
Don Guillet
SalesAid Softwar
(e-mail address removed)

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

there with VBA's help and the automatic drop-down selections, I came up wit
something that works for my problem. I only wanted to copy a certain rang
and paste it into a new row, and then clear out some individual cells (tha
do not have formulas or conditional formatting). Here's what I've come u
with that is now working