How to update data one sheet to Another Sheet through VBA in Microsoft Excel

In this article we are going to learn how to update the data from one sheet to another sheet through VBA in Microsoft Excel.

Let’s take an example to understand how and where we can use this Macro in our data to reduce our daily work.
We want to write this code for those employees who are working in a call center. When clients call them they takes basic details like User_Name, User_ID, Phone_Number, and Problem_ID.
Now we want to write the macro for when a call comes they will note down all the above mentioned details into an excel sheet, and when they click on update that data should be update in another sheet.
image 1
To update the data from one sheet to another sheet follow below given steps:-
• First we will insert command button in the worksheet.
• Go to Developer tab and then insert Command button from Activexcontrol.
• Assign below mentioned macro:-

Private Sub CommandButton1_Click()
Dim User_Name As String, User_ID As Integer, Phone_Number As Double, Problem_ID As Integer
User_Name = Range("B2")
User_ID = Range("B3")
Phone_Number = Range("B4")
Problem_ID = Range("B5")
If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = User_Name
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = User_ID
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Phone_Number
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Problem_ID
End Sub


Code Explanation:- First we had defined all the variables. Then we had defined the range for every variables, and then described the range for all the variables. And we had defined that where and in which range data will get update after clicking on Update button.


image 2
• To run the code press key F5 on the keyboard.
• When you will enter the data and will click on update button then data will get update in another sheet and we can enter new data in the sheet.
image 3
image 4
This is the way we can update the data from one sheet to another sheet through VBA in Microsoft excel.


image 48
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at


Users are saying about us...

  1. I downloaded the excel sheet, but its only adding the data its not updating the required record. Can you please tell me why…

    • Hi! The data you add in sheet1, its getting updated in sheet2 upon clicking the button. is this what is not happening in your file? if so check the sheet names. The sheets should be names as sheet1 and sheet2. or edit the code with your sheet name. Let me know if this didn’t help.

    • If they are of same type then no. Use arrays. For example, if you have 20 variables of string type then you can define a array like this.
      Dim myVars(20) as String

      if you don’t know how many variales you gonna need or not sure of its type, then use collections. Collections are always created as a variant.

      Dim myVars as Collection

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube