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

image 2Sometimes we want to use one sheet of the excel as a form that is used to get the inputs from the user. the data entered by the user, we try to save it on some other sheets that may not be accessible to the end user for the security of the data of course. In this article, we will learn how to how we can update data from one sheet to another using VBA.

So without getting any further into my boring talks, lets delve into an example to understand the concept.

Example: Update Data On Sheet2 Base on Values Entered on Sheet1

We want to write this code for those employees who are working in a call center. When clients call them they take 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 updated 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
Worksheets("Sheet1").Select
User_Name = Range("B2")
User_ID = Range("B3")
Phone_Number = Range("B4")
Problem_ID = Range("B5")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A1").Select
If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("A1").End(xlDown).Select
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
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("B2").Select
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 way we can update the data from one sheet to another sheet through VBA in Microsoft excel. I hope I was explanatory enough. If you have any doubts regarding this article or any other excel VBA article, you can ask me in the comments section below.

 

image 48

Related Articles
Getting Started With Excel VBA UserForms | The Excel UserForms are useful for getting information from the user. Here is how you should start with VBA userforms

Change the value/content of several UserForm-controls using VBA in Excel | To change the content of the userform controls use this simple VBA snippet.

Prevent a userform from closing when the user clicks the x-button by using VBA in Excel | To prevent the userform from closing when the user clicks on the x button of the form we use UserForm_QueryClose event.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

 

 

 

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.

      • What if I need to add a multiple data from sheet 1 to sheet 2, what is the code pls?
        I meat mean, my data format is by column not by row

    • 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

  2. Hello, Thank you for this macro. I also tried pressing button from sheet1 but 3rd time data did not added. It adds up to 2nd data. Please suggest. thank you

    Sub Button1_Click()

    Dim User_Name As String, User_ID As Integer, Phone_Number As Double, Problem_ID As Integer
    Worksheets("Sheet1").Select
    User_Name = Range("B2")
    User_ID = Range("B3")

    Worksheets("Sheet2").Select
    Worksheets("Sheet2").Range("A1").Select

    If Worksheets("Sheet2").Range("A1").Offset(1, 0) "" Then
    Worksheets("Sheet2").Range("A1").End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = User_Name
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = User_ID

    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("B2").Select

    End Sub

    • Use this code:

      Private Sub CommandButton1_Click()
      Dim User_Name As String, User_ID As Integer, Phone_Number As Double, Problem_ID As Integer
      Worksheets("Sheet1").Select
      User_Name = Range("B2")
      User_ID = Range("B3")
      Phone_Number = Range("B4")
      Problem_ID = Range("B5")
      Worksheets("Sheet2").Select
      Worksheets("Sheet2").Range("A1").Select
      If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
      Worksheets("Sheet2").Range("A1").End(xlDown).Select
      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
      Worksheets("Sheet1").Select
      Worksheets("Sheet1").Range("B2").Select
      End Sub

  3. How will the code be amended if the data isn't in the same column? As per your example, all the data entered is in Column B; line by line. By example, in "D1" you type / select the Department. And in "E1" today's date gets automatically pulled in. What needs to be added / changed in the code, to save e.g. the data in "D1" and "E1" and all the data in Column B to "Sheet2." It will truly be appreciated if you could assist, Thanks a million.

  4. Trupti Sonawane

    Thank You!

    I want to update 3 different file at one time which contain same data (Like PO No, PR no, And Invoice no.), is it possible by VBA, Request please help with coding..

    Thank You !

  5. Nitin Chitnis

    Dear Sir,
    Its look like just Data entry.
    I want it should be updating regularly on date basis. For example-On 1st May20.I enter some data.say about upto row no 10.On 2nd May 20.I again enter some data.how I can transfer this type of data on day to day basis.?THANKS.
    .

Leave a Reply

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

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 Youtube