Help With Copy & paste

[Aarondo]

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

 

[VoG]

What is Target?

Try

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]

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]

For me it cut from B1 to the last cell in column B containing data.

 

[Aarondo]

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]

Try

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

 

[Aarondo]

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

Any ideas? Thanks

 

[VoG]

It cuts when I run it.

 

[Aarondo]

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]

You could try

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