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

 

[Aarondo]

Hi, thanks so much for taking the time with this one. However there seems to be a slight problem. It appears to be deleting all the entries in the column. Again I think its something to do with the function being called when the column its reading from is updated.

 

[VoG]

If you are calling that procedure in a loop, or in an event procedure that runs when Backlog is changed then it will work its way down column B. Perhaps you need to ensure that it only runs once. Something like

Private Sub ChangeEvent3()
Static RunOnce As Boolean
If Not RunOnce Then
    With Worksheets("BackLog")
        .Range("B1").End(xlDown).Copy
        Worksheets("Sheet3").Range("M9").PasteSpecial Paste:=xlPasteValues
        .Range("B1").End(xlDown).ClearContents
    End With
Else
    RunOnce = True
End If
End Sub

 

[Aarondo]

Hi thanks for your helpfulness and your patience.

Im still having problems with it. If you have the time could you possibly have a look at it and see where Im going wrong.

This is my first try at coding with VBA so I dont even know if Im going about it the correct way. Basically what Im trying to achieve:

When the spreadsheet is connected to the web, cell "G9" is constantly updating. The values that are updated are then going onto a sheet called "BackLog" in "column b". Once the updated value in cell "G9" updates to 1.23 I am wanting it to launch the function to put the next one from column B on the "Backlog" sheet into cell "M9" on the main sheet.

I thaught the cut & paste method might be the best way but Im not sure.

Heres my sheet

My Spreadsheet

 

[VoG]

I think that you would be better off using one of the built in events (probably Worksheet_Change or Worksheet_Calculate). See http://www.cpearson.com/excel/Events.aspx

 

[Aarondo]

Hi thanks for your reply.

I've already used the event change to gather the data which is then put in a list.

Is there a way to do the following?

(the function is executed) it selects row 1 column B
(the function is executed) it selects row 2 column B
(the function is executed) it selects row 3 column B

but I need it to do this automatically