Help With Copy & paste

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24
Hi, Im currently trying to "cut" the first row (with a value) in a specific column and paste it into a range on a different worksheet. For some reason it works when I use "copy" but doesnt work when I use "cut" any ideas?

Heres my code

Private Sub ChangeEvent3(ByVal Target As Range)
Worksheets("BackLog").Range("B1:B65536").End(xlDown).Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet3").Range("M9")
End If
End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
What is Target?

Try

Code:
Private Sub ChangeEvent3()
Dim LR As Long
With Worksheets("BackLog")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B1:B" & LR).Cut Destination:=Worksheets("Sheet3").Range("M9")
End With
End Sub
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24
Hi thanks for your reply.

It appears to be cutting all the data from that column and not the top row (that has a value in it.

Any ideas?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
For me it cut from B1 to the last cell in column B containing data.
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24

ADVERTISEMENT

Hi, yeah thats what it is doing. What Im wanting it to do

Is look to the top of column b and select the top row that has a value in.

Eg
Row 1 is blank
Row 2 is blank
Row 3 has a value of 4

I want it to look to the top of column B and find the first cell that has a value in it ie row 3 so it would Cut the value of "4"

Thanks for your help
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub ChangeEvent3()
With Worksheets("BackLog")
    .Range("B1").End(xlDown).Cut Destination:=Worksheets("Sheet3").Range("M9")
End With
End Sub
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24

ADVERTISEMENT

Hi, it seems to be doing the same, it copies OK but wont cut for some reason.

Any ideas? Thanks
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24
Hi, hmmm its a funny one is this one.

Youre right it does seem to be cutting but its not pasting for me. I think it could be something to do with the function being called when the column that its reading the numbers from gets updated with another number. Im wondering if I could say "cut the number" and then paste the number rather than it just doing it all at once, but how do I do that with an if function. Cos Im having trouble with the if statement.

eg If (condition true) then (perform task) then (perform another task) how would I do that.

Thanks for your help
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You could try

Code:
Private Sub ChangeEvent3()
With Worksheets("BackLog")
    .Range("B1").End(xlDown).Copy
    Worksheets("Sheet3").Range("M9").PasteSpecial Paste:=xlPasteValues
    .Range("B1").End(xlDown).ClearContents
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top