HI, Here is the required macro to search a field and if value found copy the whole row into another...
I found a solution like this :Records in between two specific dates easily can be filtered using drop-down lists ....
I found a solution as follows: Watch the video to see how it's done and download example file : https://youtu.be/SZduAoOtiS0
I have sorted the above problem. Code is given below... [code] Sub seperate() Call CleanSheets MsgBox "1 of 10 Cleaning...
Hi, Here is the VBA code to Split the Workbook into different Files by Each Sheet in the file and...
Hi, You can loop through the Sheet using Workbook Sheets Collection. See the Updated Code below: [code] Sub sbDeleteRows() 'Array...
Hi, We can follo several methods to fill the unique values from the worksheet to ListBox. Here is my preferred...
Hi I have been struggling creating a VBA code that will do the following:
I have two spreadsheets and would like to copy the value in WB1 (column/row) AD26 which is “Y” based upon the person’s name in C3 (WB1) and the course name in D3 (WB1) and automatically open WB2 (C:documenttraining.xls), which has one worksheet (“trainingcoursecompleted”) has all the people names (column A) and the courses they have taken (column B) and paste the value (“Y”) in the cell that matches the name in WB1 (C3) and course name D3 (WB1) then closes the WB2 automatically.
I am hoping you understand what I am trying to accomplish but if I am not clear please let me know so I can try and explain in more detail.
I am hoping that someone can help me as I am totally lost in how to create this VBA or if it can even be done.
Here is my understanding based on your description:
We can updated the workbooks in many ways: Here are the most common approach to update workbooks
Set wb2 = Workbooks.Open("C:documenttraining.xls")
blnUpdated = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
If ThisWorkbook.Sheets("Sheet1").Range("C3") = .Range("A" & i) _
And ThisWorkbook.Sheets("Sheet1").Range("D3") = .Range("B" & i) Then
.Range("C" & i) = ThisWorkbook.Sheets("Sheet1").Range("AD26")
blnUpdated = True
Application.DisplayAlerts = False
Application.DisplayAlerts = True
If blnUpdated = False Then
MsgBox "No Match Found", vbExclamation, "Sorry!"
MsgBox "Updated Successfully", vbInformation, "Done"
How this works?
You can call this macro when you change the D3 value. Follow the below steps to and call this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("D3").Address Then
This is great and thank you very much. I was just looking over your code and am wondering if there is a way to modify the code to look for the course name in row 1 for example course GIS is D1, course OAS is E1, course XYZ is F1 (this would be in the training completed.xls workbook)? The persons name would be in one column like you have in your example. So if in Training.xls spreadsheet, they completed course GIS (it would activate the code by pressing on a button ), the code would open “trainingcoursecompleted” search for the person’s name and the course name (GIS) and input a Y in the correct spot. Also if we have a new person that just joined the company and his name was not found in the “trainingcoursecompleted.xls” is it possible to have his name automatically inserted after the last name that is in spreadsheet (trainingcoursecompleted.xls spreadsheet). I am hoping this possible and thank you again for all your help. I am currently trying to learn how to do VBA coding and I am just amazed at the possibilities. Even more amazed at your knowledge
Is it also possible for me to download what you have created. I have tried your first code that you have created and I cannot get it to work and I know it is something that I am doing wrong.
Yes, we can do this, we can help you in creating the tool for maintaining training programs and courses. Please let us know the complete details including sample files, so that we can provide you the simple and sophisticated tool to easily handle the training programs. Please find attached the zip file, please download and extract and place the document folder in your C: drive, open WB1 and click on update button to update the record.
Feel free to contact me at: email@example.com for any custom help if you required. Thanks-PNRao!
OK I got it to work but it is not exactly what I had in mind and this is my fault for poor explanation. So it should look like this:
WB2 = “Trainingcoursecompleted.xls”
Column A = Names of people starting at row 4 and all names are in Column A
Row 3 = Courses B1 = GIS, C1 =OAS, D1 = XYZ, etc
so say in column A Row 4 the person name is Jane Doe
WB1 = “Training.xls”
Person’s name is in C3
Training Course is in D3
Training course completed I input a “Y” in AD26
Once a “Y” gets inputted or pressing the button designated as “send to Training Completed spreadsheet.
The WB2 workbook would automatically open and put a Y beside the person’s name but under the appropriate Course name.
So it would find the name Jane Doe in Column 1 and the course Name as example “OAS”and put a “Y” under the course name as an example Jane Doe’s name (in this example a “Y” would go in Row 4 column D.
And if the person’s name is not found automatically insert the person name at the end of column A and place a Y under the appropriate Course completed.
The code that you created is very close and I hope that what I envision is possible and again thank you so much for all your help. I am still amazed at what you created and hope that the additional request can be done.
Thanks for the sample files, I will give an update on this.
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).