VBA to copy from one workbook and Update to another automatically

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.

Expert Asked on July 18, 2015 in VBA: Macros.
Add Comment
7 Answer(s)

Here is my understanding based on your description:

RE: VBA code to copy of a cell value from one workbook to another automatically

Steps to Copy and Update Workbooks

We can updated the workbooks in many ways: Here are the most common approach to update workbooks

  1. We can use ADO +SQL to updated the workbooks: <a href=”http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/”>ADO in Excel VBA – Connecting to database using SQL</a>
  2. We can open the Workbook and update the records: We will use this method to update the workbook

VBA to Copy and Update Workbooks

Sub sbUpdateStatus()
Set wb2 = Workbooks.Open("C:documenttraining.xls")
blnUpdated = False
With wb2.Sheets("trainingcoursecompleted")
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
Exit For
End If
Next
End With
Application.DisplayAlerts = False
wb2.Save
wb2.Close
Application.DisplayAlerts = True
If blnUpdated = False Then
MsgBox "No Match Found", vbExclamation, "Sorry!"
Else
MsgBox "Updated Successfully", vbInformation, "Done"
End If
End Sub

How this works?

  • Set wb2 = Workbooks.Open(“C:documenttraining.xls”): Opening the target workbook which we wants to update
  • blnUpdated = False: Initiating th Boolean variable to check if record available in the target file
  • With wb2.Sheets(“trainingcoursecompleted”)
  • LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: Finding the number of records in the target file
  • For i = 2 To LastRow: Looping through each row in the target file
  • If ThisWorkbook.Sheets(“Sheet1”).Range(“C3”) = .Range(“A” & i) _
    And ThisWorkbook.Sheets(“Sheet1”).Range(“D3”) = .Range(“B” & i) Then : checking if it matches with our criteria
  • .Range(“C” & i) = ThisWorkbook.Sheets(“Sheet1”).Range(“AD26”): Updated the Status
  • blnUpdated = True ‘Yes record found
    Exit For ‘ Exit the for loop
    End If
    Next
    End With
  • DisplayAlerts = False ‘ Disable the excel application alerts while before saving
    wb2.Save ‘Saving the file
    wb2.Close ‘Closing the file
    Application.DisplayAlerts = True ‘ Enable the excel application alerts while before saving
  • If blnUpdated = False Then : check if record found
  • MsgBox “No Match Found”, vbExclamation, “Sorry!” : message if not found
    Else
    MsgBox “Updated Successfully”, vbInformation, “Done”: Message if found
    End If

Thanks-PNRao!

Keymaster Answered on July 19, 2015.
Add Comment

Now, How to make this dynamic?

You can call this macro when you change the D3 value. Follow the below steps to and call this macro:

  1. Open your worksheet module of WB1
  2. Select worksheet Change event
  3. Now call the Macro
  4. OR You can copy and paste the below code on Sheet1 module of WB1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("D3").Address Then
sbUpdateStatus
End If
End Sub

How this works
Worksheet_Change event will trigger the code when you make any changes in worksheet
The If condition will check if the changes at D3, then it will call the macro to update WB2.

Thanks-PNRao!

Keymaster Answered on July 19, 2015.
Add Comment

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

Expert Answered on July 19, 2015.
Add Comment

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.

Expert Answered on July 19, 2015.
Add Comment

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.

Document.zip

Feel free to contact me at: info@analysistabs.com for any custom help if you required.
Thanks-PNRao!

Keymaster Answered on July 19, 2015.
Add Comment

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.

Expert Answered on July 19, 2015.
Add Comment

Thanks for the sample files, I will give an update on this.

Keymaster Answered on July 20, 2015.
Add Comment
  • Found this useful?

    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).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.