Thread
:
faulty PasteSpecial line??
View Single Post
#
4
Posted to microsoft.public.excel.programming
Rowan Drummond[_3_]
external usenet poster
Posts: 414
faulty PasteSpecial line??
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
Reply With Quote
Rowan Drummond[_3_]
View Public Profile
Find all posts by Rowan Drummond[_3_]