Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA code for adding data from a sheet to another

  1. #1
    Junior Member
    Join Date
    Feb 2022
    Posts
    11
    Rep Power
    0

    VBA code for adding data from a sheet to another

    Hi all,

    please help with the VBA code for the following issue:
    I am adding some data in a sheet (Database) for some experts in a project - Year, Month, Date, Name, Activity, Sub-activity and Worked Hours.
    Is it possible to have a VBA code that every time I am manually add a new entry to this sheet automatically search and add the values from Column H "Worked hours" in the corresponding cell from another sheet (Database1)?
    I've made with the same colors the values from the first sheet with the corresponding cell from the second sheet:
    - the value from H2 cell (Database) to be added in D2 cell (Database1) because the name is John, the Activity is A.1, Sub-activity is A.1.1 and the Month is November 2022 (with yellow)
    - the value from H5 cell (Database) to be added in G20 cell (Database1) because the name is Anne, the Activity is A.2, Sub-activity is A.2.1 and the Month is February 2023 (with gray) and so on.
    Many thanks in advance!
    Liviu
    I have posted the same thread here
    https://www.mrexcel.com/board/thread...other.1231746/




    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/watch?v=vXyMScSbhk4
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 07-10-2023 at 06:54 PM.

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    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
    That may not be the final required macro. It does work with your test data and give the correct results. But it might need some final tweaking to make sure it works correctly with all possible data.
    Its intended to get you started on the sort of coding that you need.

    See how you get on, and we can then take it further when I have more time, if you need more help.



    Alan



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxzpgHWTLGj0C3q3gx4AaABAg. 9gxsUMU53al9k5c8W6QGE8
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h4ISxLD17d
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h4LdsDETim
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h32czjtyR_
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxzpgHWTLGj0C3q3gx4AaABAg
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugw_smEwvNffCPr_nrB4AaABAg. 9gvyL53lI1l9gxwd_9-V6z
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugy7vmiHsQ0oUt2QCPZ4AaABAg. 9gvoy4OW6lU9gxwxC5-rL9
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgzuX3uYmqJRtsZIbqF4AaABAg. 9gth61YhXKB9gxxCMdRLA0
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxcPC64RQGmXwO5rft4AaABAg. 9gtQLXaeg0e9gxxNuc5CCM
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwCY8vOs1DFHgYSJwF4AaABAg. 9godrFcyWYw9gxy1odpiRj
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgyL5nh_j8w70-YBoUt4AaABAg.9goMcRjwjtc9gxyslvuZKx
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwwWRgmRZNqJKptHR14AaABAg. 9go-DbayTZa9gxzPbefHXf
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwF3wECwc8tVoRmz6B4AaABAg. 9go-5xLQM8P9gxzmB7nkVQ
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgyRDmGTHnMdT7dl_qx4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
    https://www.youtube.com/watch?v=jdPeMPT98QU
    https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 07-12-2023 at 04:57 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Junior Member
    Join Date
    Feb 2022
    Posts
    11
    Rep Power
    0
    Hi Alan,

    thank you for all your detailed explanations and for your time.
    The code provided by you works great.
    I will now start to "play" with the test data and I will try to adapt it for a more complex file.
    If there will be any problems, I hope you don't mind if I bother you again.

    Thanks again!
    Liviu


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
    https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7G-bVm8_-
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7EqbG23kg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7KvJXmK8o
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7E1gwg4Aq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79hNGvJbu
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79YAfa24T
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79M1SYH1E
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78SxhXTnR
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-12-2023 at 05:29 PM.

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Quote Originally Posted by Atlantis764 View Post
    ...I hope you don't mind if I bother you again.....
    Sure , no problem. I am happy to guide you to get a good final solution.

    I did just add some further notes for my own later reference. You may also find them useful.
    See here https://excelfox.com/forum/showthrea...ll=1#post19863
    https://www.excelfox.com/forum/showt...ll=1#post21921
    https://excelfox.com/forum/showthrea...ll=1#post19864
    https://www.excelfox.com/forum/showt...ll=1#post21922


    Note that my internet provider is messing with my internet connection just now. Usually when they do that they make a big mess and I have no internet or intermittent internet for a week!
    I will always reply to you, but it may take a few days. So don’t expect me to reply quickly for the next week or so.

    Alan


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 08-01-2023 at 01:26 PM.

  5. #5
    Junior Member
    Join Date
    Feb 2022
    Posts
    11
    Rep Power
    0
    Hi Alan,

    I tried to modify the file sent by you as follow:
    - I added a new column in the Database sheet (Amount - column I)
    - I copied the Database1 sheet and made a new one Database2

    What I am trying to achieve now (but it seems that I am not able to do) is that when I add a new row in Database to add the value from column H (Worked Hours) in the corresponding cell from Database1 (you already manage to help me with that) AND the value from column I (Amount) in the corresponding cell from Database2 - 1000 in cell F22 (I copied the VBA code from you and try to make some adjustment but it is not working).

    Could you please take a look at my file and tell me where I was wrong?

    Thanks again for all your support!

    Best regards,
    Liviu


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxzpgHWTLGj0C3q3gx4AaABAg. 9gxsUMU53al9k5c8W6QGE8
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h4ISxLD17d
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h4LdsDETim
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugz2PzvZTJyxHz70eVF4AaABAg. 9gxDYq2iiZ89h32czjtyR_
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxzpgHWTLGj0C3q3gx4AaABAg
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugw_smEwvNffCPr_nrB4AaABAg. 9gvyL53lI1l9gxwd_9-V6z
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=Ugy7vmiHsQ0oUt2QCPZ4AaABAg. 9gvoy4OW6lU9gxwxC5-rL9
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgzuX3uYmqJRtsZIbqF4AaABAg. 9gth61YhXKB9gxxCMdRLA0
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgxcPC64RQGmXwO5rft4AaABAg. 9gtQLXaeg0e9gxxNuc5CCM
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwCY8vOs1DFHgYSJwF4AaABAg. 9godrFcyWYw9gxy1odpiRj
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgyL5nh_j8w70-YBoUt4AaABAg.9goMcRjwjtc9gxyslvuZKx
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwwWRgmRZNqJKptHR14AaABAg. 9go-DbayTZa9gxzPbefHXf
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgwF3wECwc8tVoRmz6B4AaABAg. 9go-5xLQM8P9gxzmB7nkVQ
    https://www.youtube.com/watch?v=xLCWtC6UYrM&lc=UgyRDmGTHnMdT7dl_qx4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
    https://www.youtube.com/watch?v=jdPeMPT98QU
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 09-22-2023 at 05:16 PM.

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Hi,
    You got very close. In fact, it's better than that: you do have all the correct coding, It's all just as it should be. You modified the coding perfectly to suit the new requirement.

    ( You may be getting 5 and 6 a bit mixed up in your testing, but I don’t think that is the main problem )

    The main thing you are doing wrong is, I think, as follows
    The macro ( event coding ) that runs automatically when something ( anything) changes anywhere in a worksheet is pre made and pre defined by Microsoft and they give it the name Worksheet_Change. So Worksheet_Change is a special unique name. We cannot easily make a second macro to do something when a change is made in a worksheet, and if we did, Excel would probably get very confused and crash.

    Any coding you want to do anything when a change occurs in a worksheet must go in that single special macro , Worksheet_Change


    So you had all the correct coding. You just need to make a few very minor changes so that you can have it all in that single macro.

    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 Or Target.Column > 9 Then Exit Sub      ' So I will ignore any changes not in column H or colimn I
    Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
        If Target.Column = 8 Then
            ' 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
        Else
        ' If it was not column 8 I changed, then it must have been column 9
        Dim WsD2 As Worksheet: Set WsD2 = ThisWorkbook.Worksheets("Database2") ' I need to have some way to tell Excel when i am referring to the other worksheet, or else Excel will by default thinkk I am referencing this workseet
        
        Rem 1 try to match the  name & Activity & Sub-activity
        Dim arrD2() As Variant: Let arrD2() = WsD2.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
        Dim strSrch2 As String: Let strSrch2 = 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 strSrch2 = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
        Dim MtchRw2 As Long
         Let MtchRw2 = Application.Match(strSrch2, arrD2(), 0) ' this tries to match the correct row in Database1
        
        Rem 2 try now to match the dates
        Dim arrDts2() As Variant: Let arrDts2() = WsD2.Evaluate("=IF({1},A1:K1)")
        Dim Dte2V2 As Long: Let Dte2V2 = Range("D" & TgRw & "").Value2 '  Value 2 gives us the number used by excel for a date
        Dim MtchClm2 As Long
         Let MtchClm2 = Application.Match(Dte2V2, arrDts2(), 1) ' this tries to match the correct row in Database2  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 WsD2.Cells.Item(MtchRw2, MtchClm2).Value = Range("I" & TgRw & "").Value
        End If
    End Sub
    Alan

    (P.S. The macro could be simplified significantly if the dates in the top row were always the same and in the same position in Database1 and Datebase2, and if the first three columns in Database1 and Datebase2 were always identical)

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 10-24-2023 at 02:59 PM.

  7. #7
    Junior Member
    Join Date
    Feb 2022
    Posts
    11
    Rep Power
    0
    Hi Alan,

    thanks again for the detailed explanations!
    The VBA code works perfectly and does exactly what it needs to do: add the values ​​from the "Worked hours" column to the correct position in Database1 and the values ​​from the "Amount" column to the correct position in Database2.
    Another thing I encountered today while working in the file sent by you was the following: I deleted an entire row from the Database sheet, but the amounts initially added to the Database 1 and Database2 remained there. Do you think it is possible to help me with this change in the VBA code so that when I delete an amount from one column or another (or the entire row in Database), that amount is also deleted from the related database (hours in Database1 and amount in Database 2)?
    The top row will always be the same and in the same position in Database1 and Database2 and all the columns will be identical because I am trying to see the same information in both databases (only worked hours in Database1 and only amount paid in Database2 for each expert). I made this mention because you said that the code can be simplified significantly.

    Many thanks again!
    Liviu



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-10-2023 at 07:29 PM.

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Hi,
    Quote Originally Posted by Atlantis764 View Post
    ..The top row will always be the same and in the same position in Database1 and Database2 and all the columns will be identical.
    This means that we don’t need two very similar main sections ( I mean we don’t need two lots of Rem 1 and Rem 2 ) since they are doing the same thing. So it matters not what worksheet we do the matching to, Datebase1 or daterbase2, the results of the found column and row would always be the same. But that is not so bad in this case as the coding works very efficiently and large more laid out coding is often better to understand and modify and learn from – from your attempt its clear your picked up well how to do things.
    But the point is that we could then use the main Rem 1 and Rem 2 section once as in the first shorter macro I did for you ( https://excelfox.com/forum/showthrea...ll=1#post19860 )
    The modification needed would be 2 main changes to the first shorter macro
    _ having a bit from the top of the last macro , ( https://excelfox.com/forum/showthrea...ll=1#post19868 ) , so that it runs when either column H or column I is changed,
    _ then the output section Rem 3 would need to be tweaked a bit so that it outputted to the correct stuff to the correct worksheet
    Here is a solution like that: ( https://excelfox.com/forum/showthrea...ll=1#post19870 )











    Quote Originally Posted by Atlantis764 View Post
    ..Do you think it is possible to help me with this change in the VBA code so that when I delete an amount from one column or another (or the entire row in Database), that amount is also deleted from the related database (hours in Database1 and amount in Database 2)?
    _ If you delete a single cells value in either column H or column I, then the existing macros will do what you want already.- That is because you have effectively changed the cells value from what it was to another value of an empty string. That new empty string value will be put in the cell, so to our eyes it will look empty. The end result is what you want.
    (In computer VBA language an “empty cell” can be truly Empty or it can have a value that to us as Humans is the same – it can have a value of a zero length string.
    So that zero length string value is put in the cell by the existing coding if you remove a value from a cell in either column H or column I, which to our human eyes looks like the cell is empty. Some smarter computer experts might explain that a bit better, but at the end of the day it all amounts to a similar final result as far as we are concerned)

    Quote Originally Posted by Atlantis764 View Post
    ..Do you think it is possible to help me with this change in the VBA code so that when I delete ….. the entire row in Database), that amount is also deleted from the related database (hours in Database1 and amount in Database 2)?
    Well, sort of…..
    _ You can allow for pretty well any event to cause something to happen. It is just a case of how complicated the final macro will end up. Sometime it’s a better idea if you try to limit what can be done to what your really need. But that is all up to you and how important it is and how much time it saves you. Any code can go wrong or have unexpected bugs in the future, so its always best to think beforehand what you really need – the more longer and more complicated a coding is, the higher likelihood of course of something unexpected occurring later. But it’s down to you to decide on all that.

    Nothing is difficult to do. But it is just a lot of simple and tedious coding and becomes inefficient the more you want to do. I am reluctant to go too far helping in this direction as the coding is not difficult, but just takes time. So I start going away from helping you and just start doing your work for you.

    I give you one example:
    Lets say you want the macro to remove from the related database (hours in Database1 and amount in Database 2) when you remove all values in columns A to I in worksheet Database

    This gets very tedious and complicated as you are removing the information you need to figure out what row and column you want to access and remove data from in Database1 and database2 !!!!!!

    To start with , towards the top of the Database worksheet code module you need variables to catch that required information before you remove it.
    Code:
    ' These are sometimes called global variable
    Dim strSrching As String
    Dim DteVee2 As Long
    Now we need to make use of another of the event macros that Microsoft already has there and lets us add to. It is the one that runs every time you select something.
    So my idea is that, in that event macro, I put coding which will store that information when you select a row in columns A to I, in one go. ( Note I am meaning selecting a range 1 row and 9 columns. If you prefer it to be when you select the entire row, that is just as possible, but different extra coding once again )

    Like this
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
        If Target.Column = 1 And Target.Columns.Count = 9 And Target.Rows.Count = 1 Then
        ' fill the global variable in case the values are about to be removed
         Let strSrching = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
         Let DteVee2 = Range("D" & TgRw & "").Value2
        Else
        End If
    End Sub
    
    Then we need this sort of thing to replace our existing coding.
    First

    _.. remove the worksheet variable assignments, and target row variable assignment further down in the existing coding because you are not allowed to assign variable more than once, and I need to do it in the new bit I will give you below
    _.. change the check in the existing coding to exit on multiple cells count to maybe check for multiple row count instead like If Target.Rows.Count <> 1 Then Exit Sub
    ( Of course you could also arrange that you could delete values in multiple rows, but once again , more and more complicated coding to allow for all that… )
    _.. possibly some other things I have not thought about at first thought. There might be some other variable assignments to remove the duplicate of, for example.

    Then
    This new coding (to replace the existing Worksheet_Change coding)
    You will need to add to that a version of the existing coding, modified a bit as I partly explained

    Code:
    Private Sub worksheet_Change(ByVal Target As Range)
    Dim WsD1 As Worksheet: Set WsD1 = ThisWorkbook.Worksheets("Database1")
    Dim WsD2 As Worksheet: Set WsD2 = ThisWorkbook.Worksheets("Database2")
        If Target.Column = 1 And Target.Columns.Count = 9 And Target.Rows.Count = 1 Then
        Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
        ' check if all cells are empty
        Dim Clm As Long, strTxt As String
            For Clm = 1 To 9
             Let strTxt = strTxt & Cells.Item(TgRw, Clm).Value
            Next Clm
            If strTxt = "" Then  ' This means all cells are empty (or have a zero length strings in them)
            ' First we need to get the row and column infomation as before ( buit the info we need is not there anymore as we just removed it!! )
                        'Dim strSrching As String ' we need to have got this filled before we removed all the info
                        ' Let strSrching = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
            Dim arrDee1() As Variant: Let arrDee1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25")
            Dim MtchRow As Long
             Let MtchRow = Application.Match(strSrching, arrDee1(), 0) ' this tries to match the correct row
            Dim arrDts() As Variant: Let arrDts() = WsD1.Evaluate("=IF({1},A1:K1)")
                        'Dim DteVee2 As Long: Let DteVee2 = Range("D" & TgRw & "").Value2 '  we need to have got this filled before
            Dim MtchColm As Long
             Let MtchColm = Application.Match(DteVee2, 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
            
            'now we can "empty" the old data
             Let WsD1.Cells.Item(MtchRow, MtchColm).Value = ""
             Let WsD2.Cells.Item(MtchRow, MtchColm).Value = ""
            Else
            End If
        Else
    '
    '
    '
    '
    '    '  here goes all the rest, the existing coding, a bit modified as necessary
    '
    '
    '
    '
    '
        End If
    End Sub




    I think you can see it’s getting complicated and tedious, and takes a while to “develop” but not difficult.

    The other thing to bear in mind is that Program “development” as we are doing is extremely inefficient. It is very good for learning purposes, ( and also very good if you are paid to be a program “developer” since you will have plenty of work to be paid for. )

    If you want to do the job efficiently it is almost always better to think very hard of what you want to do and be like 99.99% sure of everything you want to do before you start.
    Otherwise it takes a lot longer and also the final thing you come up with will be a mess of patched together bits and almost certainly not the best final solution




    Alan
    Attached Files Attached Files
    Last edited by DocAElstein; 03-10-2023 at 06:40 PM.

  9. #9
    Junior Member
    Join Date
    Feb 2022
    Posts
    11
    Rep Power
    0
    Quote Originally Posted by DocAElstein View Post

    The other thing to bear in mind is that Program “development” as we are doing is extremely inefficient. It is very good for learning purposes, ( and also very good if you are paid to be a program “developer” since you will have plenty of work to be paid for. )

    If you want to do the job efficiently it is almost always better to think very hard of what you want to do and be like 99.99% sure of everything you want to do before you start.
    Otherwise it takes a lot longer and also the final thing you come up with will be a mess of patched together bits and almost certainly not the best final solution




    Alan
    Hi Alan,

    Thanks again for all your help and support!
    You are absolutely right: it is very good to determine exactly what you want from the beginning of the project before you start working.
    The idea from which I initially started with this project was the following:
    I made a User form in excel (and I was very proud of my achievement ) in which I entered the data (the fields from the User Form were exactly the dates from the top row of the Database) for each expert at the end of each month.
    Every time I save the data in the User form, they are automatically written as a new row in the Database sheet, the values ​​from the "Worked hours" column to the correct position in Database1 and the values ​​from the "Amount" column to the correct position in Database2.
    I managed to make the VBA code so that all the steps above work correctly.
    After about a year of working with this file and several hundred recordings in it, I realized that it is a repetitive work at the end of each month and that this activity is very time-consuming.
    Reaching this moment (I admit that I should have thought faster) I thought that I could finish the job much faster if I actually copy-paste the previous data from the Database - rows previously entered (without entering the data in the User form) and just changed the month, hours and amount.
    That's the reason why I asked for your help in this post.
    I will try to look very carefully at the explanations given by you in the previous post and modify what you suggested. I admit that I already tried to modify it but I couldn't get it to work (due to the fact that I'm a rookie) not because your explanations weren't very accurate.
    I sincerely hope that over the weekend I will reach a positive result.

    Thanks again for all your help and support!

    Best regards,
    Liviu

  10. #10
    Junior Member lucilla's Avatar
    Join Date
    Apr 2023
    Posts
    1
    Rep Power
    0
    Code:
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, fn As Range
    Set sh1 = targetWorkbook.Sheets(SheetName) 'Master
    Set sh2 = QlikWorkbook.Sheets(1) 'Qlik Data
    lr = sh1.Cells(Rows.Count, 1).Row
        For i = 2 To lr 'Assumes header on new sheet.
            With sh2
                  Set fn = sh1.Range("A:A").Find(.Cells(i, 1).Value, .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious)
                    If Not fn Is Nothing Then
                        .Cells(i, 1).EntireRow.Copy fn
                    End If
            End With
        Next

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-13-2023 at 10:44 PM. Reason: code tags
    Barcode Label Maker by : https://www.barcodelabelmaker.org

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2014, 04:22 PM
  2. Adding function without messing up original code
    By peter renton in forum Excel Help
    Replies: 5
    Last Post: 12-24-2013, 01:15 PM
  3. adding entries into combobox with code
    By paul_pearson in forum Excel Help
    Replies: 1
    Last Post: 07-23-2013, 01:01 PM
  4. Replies: 14
    Last Post: 06-24-2013, 06:17 PM
  5. Adding charts via code to a protected sheet
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 11-14-2012, 05:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •