With much help from Norman a couple of days ago, I have arrived at the
following
piece of code.

Sub moveColsBelow()
'move all data in pre-defined columns, below active cell, down one row
'to create empty cells for a new record.

On Error GoTo err_handler

Range("I" & ActiveCell.Row + 1 & ": K" _
& ActiveSheet.Range("I65536").End(xlUp).Row).Cut

Range("I" & ActiveCell.Row + 2).PasteSpecial xlPasteValues
'This sub freezes on the above line with an error number of 1004
'"PasteSpecial method of Range class failed"
'If the message box in the err_handler is turned off then it works
'tolerably well but requires an {ENTER} to finish.

Exit Sub
err_handler:
MsgBox "Error Number " & Err.Number & "Occurred"
End Sub


This is what I start with:
.. I J K
10 20 20 20
11 71 72 103
12 21 21 21
13 22 22 22
14 23 23 23
15 24 24 24
16 25 25 25
17 26 26 26
18 27 27 27
19 28 28 28
20 29 29 29
21 30 30 30
22
23



after selecting a cell in row 10 and running "moveColsBelow()"
this is what I get and also nearly what I want.

.. I J K
10 20 20 20
11
12 71 72 103
13 21 21 21
14 22 22 22
15 23 23 23
16 24 24 24
17 25 25 25
18 26 26 26
19 27 27 27
20 28 28 28
21 29 29 29
22 30 30 30
23

But I need to press {Enter} in order for the routine to complete.
I have tried many mutations of the SendKeys command and they do
not work. But I do not think that the answer lies with "SendKeys".
I think the PasteSpecial line is faulty.

Also, I would like the routine to finish by making, in this case,
I11 the Active cell - ready to accept new data from the user.

Any help would be greatly appreciated.

Geoff

try this, you can leave off the default values if you want to

Range("I" & ActiveCell.Row + 2).PasteSpecial Paste:=xlPasteValues,
Operation:=xlSubtract, SkipBlanks _
:=False, Transpose:=False

--


Gary


wrote in message
oups.com...
With much help from Norman a couple of days ago, I have arrived at the
following
piece of code.

Sub moveColsBelow()
'move all data in pre-defined columns, below active cell, down one row
'to create empty cells for a new record.

On Error GoTo err_handler

Range("I" & ActiveCell.Row + 1 & ": K" _
& ActiveSheet.Range("I65536").End(xlUp).Row).Cut

Range("I" & ActiveCell.Row + 2).PasteSpecial xlPasteValues
'This sub freezes on the above line with an error number of 1004
'"PasteSpecial method of Range class failed"
'If the message box in the err_handler is turned off then it works
'tolerably well but requires an {ENTER} to finish.

Exit Sub
err_handler:
MsgBox "Error Number " & Err.Number & "Occurred"
End Sub


This is what I start with:
. I J K
10 20 20 20
11 71 72 103
12 21 21 21
13 22 22 22
14 23 23 23
15 24 24 24
16 25 25 25
17 26 26 26
18 27 27 27
19 28 28 28
20 29 29 29
21 30 30 30
22
23



after selecting a cell in row 10 and running "moveColsBelow()"
this is what I get and also nearly what I want.

. I J K
10 20 20 20
11
12 71 72 103
13 21 21 21
14 22 22 22
15 23 23 23
16 24 24 24
17 25 25 25
18 26 26 26
19 27 27 27
20 28 28 28
21 29 29 29
22 30 30 30
23

But I need to press {Enter} in order for the routine to complete.
I have tried many mutations of the SendKeys command and they do
not work. But I do not think that the answer lies with "SendKeys".
I think the PasteSpecial line is faulty.

Also, I would like the routine to finish by making, in this case,
I11 the Active cell - ready to accept new data from the user.

Any help would be greatly appreciated.

Geoff

Thanks for the effort Gary..... but that does not work either.

I am wondering if there is a way to do it that does not
require the use of the "Paste Special" method. Though I suspect if
there is, it will be slow.

Geoff

Hi Geoff

What about trying it like this;

Sub moveColsBelow()
Range(Cells(ActiveCell.Row, "I"), Cells(ActiveCell.Row, "K")). _
Insert xlDown
Range("I" & ActiveCell.Row).Select
End Sub

Hope this helps
Rowan

wrote:
With much help from Norman a couple of days ago, I have arrived at the
following
piece of code.

Sub moveColsBelow()
'move all data in pre-defined columns, below active cell, down one row
'to create empty cells for a new record.

On Error GoTo err_handler

Range("I" & ActiveCell.Row + 1 & ": K" _
& ActiveSheet.Range("I65536").End(xlUp).Row).Cut

Range("I" & ActiveCell.Row + 2).PasteSpecial xlPasteValues
'This sub freezes on the above line with an error number of 1004
'"PasteSpecial method of Range class failed"
'If the message box in the err_handler is turned off then it works
'tolerably well but requires an {ENTER} to finish.

Exit Sub
err_handler:
MsgBox "Error Number " & Err.Number & "Occurred"
End Sub


This is what I start with:
. I J K
10 20 20 20
11 71 72 103
12 21 21 21
13 22 22 22
14 23 23 23
15 24 24 24
16 25 25 25
17 26 26 26
18 27 27 27
19 28 28 28
20 29 29 29
21 30 30 30
22
23



after selecting a cell in row 10 and running "moveColsBelow()"
this is what I get and also nearly what I want.

. I J K
10 20 20 20
11
12 71 72 103
13 21 21 21
14 22 22 22
15 23 23 23
16 24 24 24
17 25 25 25
18 26 26 26
19 27 27 27
20 28 28 28
21 29 29 29
22 30 30 30
23

But I need to press {Enter} in order for the routine to complete.
I have tried many mutations of the SendKeys command and they do
not work. But I do not think that the answer lies with "SendKeys".
I think the PasteSpecial line is faulty.

Also, I would like the routine to finish by making, in this case,
I11 the Active cell - ready to accept new data from the user.

Any help would be greatly appreciated.

Geoff

Gary and Rowan. Thankyou very much for your time.

Very elegant Rowan....... thanks that worked a real treat.

Now I am wondering how to move them back up again.

Could something like
Delete xlUp work.?

I'm off to give it a try.

Geoff.

You're welcome Geoff, and Delete xlUp will work in exactly the opposite
way to Insert xlDown but of course you will loose whatever data you have
in columns I, J and K in the activecell's row.

Regards
Rowan

wrote:
Gary and Rowan. Thankyou very much for your time.

Very elegant Rowan....... thanks that worked a real treat.

Now I am wondering how to move them back up again.

Could something like
Delete xlUp work.?

I'm off to give it a try.

Geoff.

Yep..... That works

Sub moveStuffUp()
Range(Cells(ActiveCell.Row, "I"), _
Cells(ActiveCell.Row, "K")).Delete xlUp
Range("I" & ActiveCell.Row).Select
End Sub

Geoff