I am working on a VBA project which focuses on to organizing the information of clients of any company. I am trying to learn the VBA language through internet but ran into a slight problem, so I need help with it.

Purpose of the code: I have different worksheets in a single workbook for different clients and every workbook has a certain information stored in it. Every time the existing client orders product from company, a record of the date of the recent deal done is stored on their worksheet (past dates are not deleted, instead new date entry is created)). Since a lot of deals can take place every day, It is hard to maintain the entire excel sheet manually. Hence, I am trying to create a solution through Master Client Book Worksheet which will have the name and date (date on which the recent business was conducted) of the clients. Now, every time I make the changes to the date on the Master Client Worksheet, a new entry is registered in the clients Worksheet automatically through the code I have listed below.

Problem: The issue which I am currently facing here is that since there are so many clients and every client sheet has two three businesses going on (Different date for every business), I will have to copy and paste this code in the VBA Project multiple times. I am looking for a way which can reduce this redundancy and make the code work for every worksheet through the one single code.

Secondly, I want the same to work for the ID as well, When the values of ID, Order Req date and Order Dispatch date are filled, the values should be copied to the client's sheet as well.

It will be great if anyone can help me with this as soon as possible.

The Code that I wrote: (Only the first part highlighted in Italics is actual code, other code is just the copy of it with minor to no tweaks. This is done to show the redundancy in the code)

For Client A:

Business 1:

Order 1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D4 ")) Is Nothing Then

ActiveCell.Offset(-1, 0).Activate

a = Sheets("Sumeet").Cells(Rows.Count, "F").End(xlUp).Row + 1

Sheets("Sumeet").Range("F" & a).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

End If

Order 2:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D4 ")) Is Nothing Then

ActiveCell.Offset(-1, 0).Activate

a = Sheets("Sumeet").Cells(Rows.Count, "F").End(xlUp).Row + 1

Sheets("Sumeet").Range("F" & a).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

End If

Business 2:

Order 1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D6:D6")) Is Nothing Then

ActiveCell.Offset(-1, 0).Activate

a = Sheets("Sumeet").Cells(Rows.Count, "F").End(xlUp).Row + 1

Sheets("Sumeet").Range("F" & a).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

End If

Same is done for Client 2, Client 3 .. .. .. .. Client n.

End Sub

Excel Sheet Pictures are attached to understand the situation better

M4 : X A B D E F G G H K L M N 0 P P O 1 Client Name: 2 ID 3 4 Business 1 5 6 6 7 7 8 9 Model No. labc def Ighi Model No. jffd Order 1 - Summary Order Reg Receivec Order Dispatched 10/18/2021 12-Oct-21 2/26/2021 28-Oct-21 2021-11-10 5-Nov-21 213 654 554 Order 2 - Summary ID Order Reg Rec Order Dispatched 654 5/14/2021 8-Jun-21 564 6/7/2021 2-Jul-21 455 30-Jun-21 25-Jul-21 dfgd dfgd 10 11 12 13 14 15 16 17 18 19 20 Business 2 21 22 23 24 25 Model No. Model No. Order 1 - Summary Order Req Receivec Order Dispatched 6/23/2020 23-Jul-20 7/23/2020 22-Aug-20 8/22/2020 23-Sep-20 Model No.ID fgfdg 656+ fgfdg 22 asdr 3232 iou Order 2 - Summary ID Order Req Rec Order Dispatched 3565 8/30/2019 31/Jan/20 233 11/8/2019 31/Mar/20 3232 3/20/2020 8/Sep/20 wew fgf Jadea ID 878 848 115 Order 3 - Summary Order Req Re Order Dispatched 6/7/2021 2-Jul-21 30-Jun-21 25-Jul-21 7/23/2021 17-Aug-21 hjmh Igng 26 27 28 29 30 31 32 33 34 35 36 37 38 Master Client A Client B C18 B C D E F G H K Position Contact ID Dates A 1 Employer 2 3 Ordern Order 1 Order Dispatch Order 2 Order Reg Order Dispatch Order Req 4 4 5 Business 1 dfsd Date Date Date Date Client 1 XXX-XXX-XXXX Business 2 sdsdf Date Date Date Date 6 7 8 9 10 11 12 Business 1 erwe Date Date Date Date Client 2 XXX-XXX-XXXX Business 2 hghn Date Date Date Date 13 1 1 14 15 16 17 1 18 ! Client n 19 20 21 22 23 24 25 26 27 28 29 30 ! ! 1 ! 31 32 33 34 1 1 1 ! 35 36 37 38 Master Client A A Client B M4 : X A B D E F G G H K L M N 0 P P O 1 Client Name: 2 ID 3 4 Business 1 5 6 6 7 7 8 9 Model No. labc def Ighi Model No. jffd Order 1 - Summary Order Reg Receivec Order Dispatched 10/18/2021 12-Oct-21 2/26/2021 28-Oct-21 2021-11-10 5-Nov-21 213 654 554 Order 2 - Summary ID Order Reg Rec Order Dispatched 654 5/14/2021 8-Jun-21 564 6/7/2021 2-Jul-21 455 30-Jun-21 25-Jul-21 dfgd dfgd 10 11 12 13 14 15 16 17 18 19 20 Business 2 21 22 23 24 25 Model No. Model No. Order 1 - Summary Order Req Receivec Order Dispatched 6/23/2020 23-Jul-20 7/23/2020 22-Aug-20 8/22/2020 23-Sep-20 Model No.ID fgfdg 656+ fgfdg 22 asdr 3232 iou Order 2 - Summary ID Order Req Rec Order Dispatched 3565 8/30/2019 31/Jan/20 233 11/8/2019 31/Mar/20 3232 3/20/2020 8/Sep/20 wew fgf Jadea ID 878 848 115 Order 3 - Summary Order Req Re Order Dispatched 6/7/2021 2-Jul-21 30-Jun-21 25-Jul-21 7/23/2021 17-Aug-21 hjmh Igng 26 27 28 29 30 31 32 33 34 35 36 37 38 Master Client A Client B C18 B C D E F G H K Position Contact ID Dates A 1 Employer 2 3 Ordern Order 1 Order Dispatch Order 2 Order Reg Order Dispatch Order Req 4 4 5 Business 1 dfsd Date Date Date Date Client 1 XXX-XXX-XXXX Business 2 sdsdf Date Date Date Date 6 7 8 9 10 11 12 Business 1 erwe Date Date Date Date Client 2 XXX-XXX-XXXX Business 2 hghn Date Date Date Date 13 1 1 14 15 16 17 1 18 ! Client n 19 20 21 22 23 24 25 26 27 28 29 30 ! ! 1 ! 31 32 33 34 1 1 1 ! 35 36 37 38 Master Client A A Client B