Thread: Auto copy & paste

I need a way to automatically copy and paste cells from a qualifying previous row.

For example:
1) I Enter data (text only) in cell B100 and exit that cell.
2) Cell B20 (closest & previous) happens to match data entered in B100
3) First 12 cells in row 20 are copied and pasted over first 12 cells in row 100
4) Row 100 is used for additional manual processing then deleted

Rows 100 and 20 are examples... other combinations are possible

I'm fairly good with excell and a rooky with VBA, but I'm learning.

Thanks docMed. I'll give your suggestion a try to get things rolling.

This is indeed simple, but a little short of demand. What I meant by "any combination" was the the first occurance could be any row in the sheet followed by more occurances. In my example row 100 is a copy of row 20... row 101 could be a copy of row 5... row 102 a copy of row 15 and so on.
They would usually be stacked like this, but not necessarily. That's why I hinted at VBA. Wouldn't a ready-armed tool be more user friendly than propogating formulas prior to an event?

Wouldn't using formulas leave the established stack subject to alteration due to changes made to any or all rows above the stack. This dynamic might be useful in some respects, but not what I would wan't in my current need.

Originally Posted by clinet

3) First 12 cells in row 20 are copied and pasted over first 12 cells in row 100
4) Row 100 is used for additional manual processing then deleted
....

you mean:

- after enter data in cell Bn (n=100 in your example)
- copy value of the first 12 cells Ak:Lk from the row k (k=20 in your example) in that cell Bk contains value =Bn (B20=B100) -- search the condition in database B2:B99

and continue for the next row with B100 added to the database (B2:B100 for B101)

------------
is that right?

You desire the solution by formula? or VBA?

How large is your data? (for example if the database more than thousands - the VBA solution should be consisdered)

the last but not least, you should post a sample file (with dummy data) in order to it is easy to understand and take advantage for others' help

TigerTiger. Here is a sample file with three events.

5 AA G456272534 AAA AAAAAAA AAAAAAAA AAAAAAA AAA AA

15 BB HR667583TT BBB BBBBBBB BBBBBBBB BBBBBBB BBB BB

20 CC SRT2675453 CCC CCCCCCC CCCCCCCC CCCCCCC CCC CC


100 BB HR667583TT BBB BBBBBBB BBBBBBBB BBBBBBB BBB BB
101 CC SRT2675453 CCC CCCCCCC CCCCCCCC CCCCCCC CCC CC
102 AA G456272534 AAA AAAAAAA AAAAAAAA AAAAAAA AAA AA


I enter HR667583TT in B100 and upon exiting B15 matches B100 so row 15 is copy-paste over row 100.
I enter SRT2675453 in B101 and upon exiting B20 matches B101 so row 20 is copy-paste over row 101
I enter G456272534 in B102 and upon exiting B5 matches B102 so row 5 is copy-paste over row 102.

Obviously there are rows of data between the three I reference for this example, and the stack could be lengthy before it's deleted.
I have thousands of rows above the resultant stack, so VBA seems the best.

Try test the following code,
Right click on sheet tab (the sheet in that you want auto input data) and select view code, then paste the following code in to

Private Sub Worksheet_Change(ByVal Target As Range)
   
  If Target.Cells.Count > 1 Then Exit Sub
  Const rStart = 2:  Const cEnd = 10
  
  If Not Intersect(Target, Range(Cells(rStart, 2), Cells(Rows.Count, 2))) Is Nothing Then
    MsgBox "vo day rui: " & Target.Offset(-1).Value
    Application.EnableEvents = False
    Dim iR As Long
    For iR = Target.Offset(-1).Row To rStart Step -1
        If Target.Offset(-1).Value = Cells(iR, 2).Value Then
            Range(Cells(Target.Row, 1), Cells(Target.Row, cEnd)).Value _
                  = Range(Cells(iR, 1), Cells(iR, cEnd)).Value
            Application.EnableEvents = True
            Exit Sub
        End If
    Next
  End If
  Application.EnableEvents = True
End Sub

Or get the example from attached file

Originally Posted by clinet

Am I missing seeing a loop that would scan further up than that... all the way to the top if necessary? .

No, of course.

In that code, it run only until meet the row matching the condition

Try an alternative, it may be faster by using Find method (priority for the update record)
Right click on sheet tab (the sheet in that you want auto input data) and select view code, then paste the following code in to

Private Sub Worksheet_Change(ByVal Target As Range)
   
  If Target.Cells.Count > 1 Then Exit Sub
  Const rStart = 2:  Const cEnd = 10
  
  If Not Intersect(Target, Range(Cells(rStart, 2), Cells(Rows.Count, 2))) Is Nothing Then
   
    Application.EnableEvents = False
    Dim iR As Long, rgnF As Range
    Set rgnF = Nothing
    Set rgnF = Range(Cells(Target.Offset(-1).Row, 2), Cells(rStart, 2)).Find(What:=Target.Value, _
             LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
             MatchCase:=False, SearchFormat:=False)

    On Error GoTo 0
    If Not rgnF Is Nothing Then
            Range(Cells(Target.Row, 1), Cells(Target.Row, cEnd)).Value _
                  = Range(Cells(rgnF.Row, 1), Cells(rgnF.Row, cEnd)).Value
    End If
  End If
  Application.EnableEvents = True
End Sub

Splendid tigertiger! Your latest code worked perfectly!

Some day this rooky might be proficient like you

Thank you. I will for sure add to your reputation.