Prev: don't know what function to use
Next: Follow up question about consolidating dups and sums
From: climate on 3 Apr 2010 06:33
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
From: tompl on 3 Apr 2010 10:08
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

From: Rick Rothstein on 3 Apr 2010 10:08
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

--
Rick (MVP - Excel)



"climate" <climate(a)discussions.microsoft.com> wrote in message
news:93835349-DD78-4166-9DFE-45416AA0474D(a)microsoft.com...
> 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

From: tompl on 3 Apr 2010 10:12
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


From: climate on 3 Apr 2010 14:59
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" wrote:

> 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
>
>
 |  Next  |  Last
Pages: 1 2
Prev: don't know what function to use
Next: Follow up question about consolidating dups and sums