Copy Information from one sheet to another

[adam]

How can I make the values that I enter into the columns (D, H, I, M, N, O, P) of the worksheet “Inve” to be copied into the columns (D, E, F, J, K, L, M) of the worksheet “Donation” when I write the donor ID in column C of the worksheet “Donation” depending upon the last date the donor ID was entered to the worksheet “Inve”

In short, I want the columns(D, H, I, M, N, O, P) of the row which contains the last date donor ID from the worksheet "Inve" to be copied to the columns (D, E, F, J, K, L, M) that contains last date row where Donor ID is entered to the worksheet “Donation”.

I have attached the document for your reference.

Thanks in advance

Regards
Adam

[HansV]

You're still trying to create a database application. Microsoft Access would be much more suitable for that.

The sample workbook isn't very useful, since it contains only one Donor ID.

[adam]

No Its not like that. pls dont think so broadly. I don't think my question goes to access
I have modified my sample book for a clear definition of what I'm asking for.
I have also quoted in the sample book attached; what I want.

pls see the modified sample book & let me know your response.

regards
Adam

[HansV]

Yes, you are trying to imitate an Access database in Excel...

Try this code in the worksheet module of the Donation sheet:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim wsh As Worksheet
  Dim rngCell As Range
  Dim strID As String
  Dim rngFound As Range
  Dim r As Long
  Dim n As Long
  Dim dtmMax As Date
  Dim m As Long
  Set wsh = Worksheets("Inve")
  m = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Row
  If Not Intersect(Range("C10:C" & Rows.Count), Target) Is Nothing Then
    For Each rngCell In Intersect(Range("C10:C" & Rows.Count), Target)
      strID = rngCell.Value
      dtmMax = DateSerial(1900, 1, 1)
      n = 0
      For r = 9 To m
        If wsh.Range("D" & r) = strID Then
          If wsh.Range("A" & r) > dtmMax Then
            n = r
            dtmMax = wsh.Range("A" & r)
          End If
        End If
      Next r
      If n = 0 Then
        rngCell.Offset(0, 1).Resize(1, 3).ClearContents
        rngCell.Offset(0, 7).Resize(1, 4).ClearContents
      Else
        rngCell.Offset(0, 1) = wsh.Range("G" & n)
        rngCell.Offset(0, 2) = wsh.Range("H" & n)
        rngCell.Offset(0, 3) = wsh.Range("I" & n)
        rngCell.Offset(0, 7) = wsh.Range("M" & n)
        rngCell.Offset(0, 8) = wsh.Range("N" & n)
        rngCell.Offset(0, 9) = wsh.Range("O" & n)
        rngCell.Offset(0, 10) = wsh.Range("P" & n)
      End If
    Next rngCell
  End If
End Sub

[adam]

Thanks for the help Hans. I do really appreciate it.
By the way, I hope my questions never turn into a challenge.

[HansV]

This thread has become very long. I've moved the latest post to a new thread of its own: Fill text box in userform.

This thread is now locked.