Data moving 2

[climate]

I have following code:
Sub climate()
Dim x As Long, y As Long
x = Range("X" & Rows.Count).End(xlUp).Row
y = InputBox("enter the row number to paste")
Range("A2:BK2" & x).Copy
Range("A" & y).PasteSpecial
MsgBox "Updated"
End Sub

I want to cut data of range(A2:BK2 to end row related) and then paste to row
number which determine in Box. when i run above code on some of sheets, error
message appear(Run Time error 1004). please note that, this code with cut
command not run, only with copy command.

any help will be greatly appreciated.
climate

 

[tompl]

I made a few notes in your code:

Dim x As Long, y As Long
'One character variables are risky.
x = Range("X" & Rows.Count).End(xlUp).Row
'Finds the last used row in column X
y = InputBox("enter the row number to paste")
Range("A2:BK2" & x).Copy
'Invalid - if x is 40 then the range would be 240 _
Remove the 2 after BK.
Range("A" & y).PasteSpecial
'Why PasteSpecial without telling what is special
'Also, you cannot PasteSpecial cell that were Cut.
MsgBox "Updated"
End Sub


Then I wrote and tested the following. It should work better.

Sub climate2()

Dim xLng As Long, yLng As Long
xLng = Range("X" & Rows.Count).End(xlUp).Row
yLng = InputBox("enter the row number to paste to:")
Range("A2:BK" & xLng).Cut 'or Cut (Can't PasteSpecial Cut cells)
Range("A" & yLng).PasteSpecial Paste:=xlPasteValues 'Or just Paste
Application.CutCopyMode = False
Range("A1").Select
MsgBox "Updated"

End Sub

Good luck,

Tom

 

[Rick Rothstein]

It would have helped if along with telling us what the error message was if
you also told us what line it occurred on (which line did the debugger
highlight). I have no idea if the following is the root of your problem or
not, but this line is not doing what you think...

Range("A2:BK2" & x).Copy

That is not how you construct the range you want. The "2" at the end of
"BK2" should not be there. For example, if "x" evaluates to, say, 999, then
your range becomes A2:BK2999, not A2:BK999... that is some 2000 rows below
the last piece of data in Column X. I believe you want this instead...

Range("A2:BK" & x).Copy

 

[tompl]

Woops, I forgot to change cut back to copy. Use this:

Sub climate2()

Dim xLng As Long, yLng As Long
xLng = Range("X" & Rows.Count).End(xlUp).Row
yLng = InputBox("enter the row number to paste")
Range("A2:BK" & xLng).Copy 'or Cut (Can't PasteSpecial Cut cells)
Range("A" & yLng).PasteSpecial Paste:=xlPasteValues 'Or just Paste
Application.CutCopyMode = False
Range("A1").Select
MsgBox "Updated"

End Sub

 

[climate]

Hi tom
Thank you
I tested your code, first problem (run time error)is solved, but desired
range dosen't cut, and overlap to prior data. i need to move desired range
and paste to new position based on row number in Box.

 

[tompl]

I did not fully understand your question before, but this should work for you:

Sub climate3()

Dim xLng As Long, yLng As Long
xLng = Range("X" & Rows.Count).End(xlUp).Row
yLng = InputBox("enter the row number to paste")
Range("A2:BK" & xLng).Cut Range("A" & yLng)
Application.CutCopyMode = False
Range("A1").Select
MsgBox "Updated"

End Sub

 

[climate]

Hi
Thank you very much, your code work's correctly.

regards

 

[climate]

Hi
Thank's Rick, your discussion is right.

regards