Automatically copy and paste

  • Hi all. I have calculated figures in rows A1: I would like to automatically copy and paste as values to the the next free cell in column M as the calculated figures in column A are added to. So, the next calculated figure in row A2 is automatically copied and pasted as a value to M2 etc. etc



    Any help appreciated.


    David

  • Hi Tanis,


    Excuse me for seeming a bit dim but why not put "=A1" in M1 and copy down?


    Are you after something more complicated?


    John

  • Yes, a perfectly valid question. The problem is that this is going to be used by computer illiterate operators in a manufacturing environment. I need to make the collection of the data as simple and painless as possible, hence my request.

  • Hi, Tanis


    When A1 changes, same value should be posted to the first available cell in col.M.
    Then A2 changes, same value to the next cell in col. M
    Is it possible A1 changes again?
    What would you like to do when it happens?

  • No, that won't happen. It is just a case of tagging the data on to the end, so that I build up a a long list of figures.

  • In cell M1


    =IF(ROW(A1)<=COUNT($A$1:$A$1000),INDIRECT("A"&SMALL(IF($A$1:$A$1000<>0,ROW($A$1:$A$1000)),ROW(A1))),"")


    holding down Ctrl+Shift and Enter to get out from the formula bar


    then drag down to row 1000th


    I'm not sure if this is what you want.


    Or,


    VBA, try attached.
    with Sheet1
    When value of last row of col.A changes, the value appear in the last row of col.M.


    Cell N1 has bbeen used as working cell.


    hope it works

  • Sorry for not replying sooner, have had a day off. Anyhow, this formula will do the trick. Couldn't download attached spreadsheet as the firewall wouldn't let me. Thanks


    David

  • Here's vba codes
    Paste all the code onto sheet module


    '-----------------


    Public lastA As Long


    Private Sub Worksheet_Activate()
    Dim i As Long


    Application.EnableEvents = False
    With Range("n1")
    .FormulaArray = "=MAX(IF((A1:A65535<>"""")*(A1:A65535<>0),ROW(A1:A65535)))"
    .Font.ColorIndex = 2
    End With
    lastA = Range("n1").Value
    Application.EnableEvents = True
    End Sub


    Private Sub Worksheet_Calculate()
    Dim lastM As Long, lastAA As Long, i As Long
    Application.EnableEvents = False
    If Range("n1").Value = 0 Then GoTo e
    lastAA = Range("a65536").End(xlUp).Row
    For i = lastAA To 1 Step -1
    If (Cells(i, 1).Value <> 0) * (Cells(i, 1).Value <> "") Then
    lastAA = Cells(i, 1).Row
    Exit For
    End If
    Next
    Select Case lastAA
    Case Is = lastA
    If IsEmpty(Cells(1, 13)) = True Then
    Cells(1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    Else
    lastM = Range("M65536").End(xlUp).Row
    Cells(lastM, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    End If

    Case Is > lastA
    If IsEmpty(Cells(1, 13)) = True Then
    Cells(1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    Else
    lastM = Range("M65536").End(xlUp).Row
    Cells(lastM + 1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    End If
    Case Else
    GoTo e
    End Select
    e:
    lastA = Range("n1").Value
    Application.EnableEvents = True
    Exit Sub
    Application.EnableEvents = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!