Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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

image text in transcribedimage text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Wall Street Mba

Authors: Reuben Advani

2nd Edition

007178831X, 9780071788311

More Books

Students also viewed these Accounting questions