VBA code for adding data from a sheet to another

**DocAElstein** · 03-08-2023, 04:38 AM

Hi Liviu
This is fairly easy with VBA.
Excel itself is not much more than a lot of VBA coding that automatically does things when you type stuff.

In VBA we have 2 sorts of possibilities to do coding to do stuff
Either
_ (i) we can write a macro from scratch and then run it ourselves when we want to. Those complete macros we put typically in a normal code module. We make those code modules as we need them
or
_ (ii) we can add some more coding into the existing coding written by Microsoft which automatically run when something is done. ( Often here we talk about Event Coding ). Microsoft have alredy put those code modules in.

Microsoft don’t usually let us look at the macros they wrote to make Excel work, but they do give us a way to add coding in the same place, so that way it can also be run when something is done.

so, we want to tap into the coding that runs when something is done in worksheet database. There are a few such macros. As an initial attempt, I will try the one which runs when something is changed in worksheet database.
Here is one way to get at it:

_ First, right click on the tab and select View Code :

_ Then fiddle around with both of the two drop down lists until you get the coding to do with worksheet change :

It’s this one

Code:

Private Sub Worksheet_Change(ByVal Target As Range)



End Sub

You don’t see all the coding that is already there because Microsoft don’t want us to and so they have made it invisible. That does not worry us. We just add our coding in there and it will be done when all the other stuff is done by Microsoft when you change anything in a worksheet, (specifically worksheet database in this case)
After that, its just normally VBA coding stuff

I will help get you started with some coding that runs when you change something in column H (column 8 )

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
' the next few lines arange when excactly our coding is done
    If Target.Cells.Count <> 1 Then Exit Sub ' So I will ignore any multi cell changes
    If Target.Column <> 8 Then Exit Sub      ' So I will ignore any changes not in column H
    ' If we get this far then we changed something in column H (column 8)
Dim WsD1 As Worksheet: Set WsD1 = ThisWorkbook.Worksheets("Database1") ' I need to have some way to tell Excel when i am referring to the other worksheet, or else Excel will by default think I am referencing this workseet

Rem 1 try to match the  name & Activity & Sub-activity
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
Dim strSrch As String ': Let strSrch = Range("E2").Value & Range("F2").Value & Range("G2").Value ' This gives for example,  "JohnA.1A.1.1"
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
 Let strSrch = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim MtchRw As Long
 Let MtchRw = Application.Match(strSrch, arrD1(), 0) ' this tries to match the correct row in Database1

Rem 2 try now to match the dates
Dim arrDts() As Variant: Let arrDts() = WsD1.Evaluate("=IF({1},A1:K1)")
Dim DteV2 As Long: Let DteV2 = Range("D" & TgRw & "").Value2 '  Value 2 gives us the number used by excel for a date
Dim MtchClm As Long
 Let MtchClm = Application.Match(DteV2, arrDts(), 1) ' this tries to match the correct row in Database1  using a 1 as the third argument im match will get the neartest next date match

Rem 3 use the found row and column to get the final wanted result
 Let WsD1.Cells.Item(MtchRw, MtchClm).Value = Range("H" & TgRw & "").Value

End Sub