Transfer Data in Excel From One Worksheet to Another Automatically Using VBA and Macros

In this video tutorial we are going to learn how to transfer data from one worksheet to another in Excel, automatically, using Excel VBA and Macros. So let’s get started!

🔥 Subscribe to my Channel ► http://bit.ly/1KWfq14
───────────────────────────────────
💎Check Out My Online Courses ►►►►►►►►►►►►►►►

📌Excel Dashboards & Charts Masterclass ► http://bit.ly/35lO8OK
Create Excel Dashboards, Interactive Excel Graphs and Pivot Charts with Microsoft Excel 2019 + Free Excel Templates

📌Excel Macros & VBA for Non-Programmers ► http://bit.ly/2D3Ipkw
Become a proficient Excel Macro Programmer in a very short time + Excel VBA for beginners. Excel Templates included.

📌Excel Power Pivot & DAX Masterclass ► http://bit.ly/2qwa2jB
Learn Excel Power Pivot - Power Map and Excel Power BI Tools. Build Excel Data Analyst Models with Microsoft Excel 2019

📌Top 100 Excel Hacks with Excel 2016 ► http://bit.ly/2pEMlW8
The Best 100 Excel Tips & Hacks with Microsoft Excel 2016 - Excel Templates Included

📌Excel Advanced Charts Masterclass ► http://bit.ly/2qyosQr
Learn from scratch professional Advanced Excel Charts with Microsoft Excel 2019 - Excel Templates included

📌Python Programming for Beginners ► http://bit.ly/2s1nUTy
A Python Practical Programming Course for Absolute Beginners - Learn how to Code in Python and Improve your Productivity

📌The Complete Gmail Course ► http://bit.ly/2QCaUxQ
Boost your Productivity with 25+ Gmail Techniques like Gmail Filters , Security , IFTTT . Master Email Communication
─────────────────────────────────────────────────────
In this workbook, called TransferData, I have two Sheets, sheet1 and sheet2. Although we are working in Excel 2016, the techniques that I’ll show you also works in every Excel version.
In sheet1 we have 2 headers, Name and Phone. When a user inserts data we want with the help of a button, to transfer the data into another sheet, which is sheet2 in our example. Notice that in sheet2 I’ve inserted the same headers, name and phone. Let’s go back to Sheet1. Click on developer tab and press the Insert Controls Button. Now from ActiveX controls choose the command button. Click and draw it somewhere there. Great!
Now right-click and go to properties. The Properties window appears on your left. Find Caption Property and write Transfer. If you want you can change the format of the button and the text, from these properties. Close the window and double-click on the button.
The VBA window appears with the command button window and the click event. We will start by defining and writing the appropriate variables. The first variable is CustomerName. So let’s type in Dim CustomerName as string. Type comma and type Customer Phone as String also.
The first step is to select sheet1. Let’s start typing: Worksheets parenthesis double quotes sheet1 which is the name of our worksheet, close quotes and parenthesis. Type dot then Select. Press enter.
Now we have to define the range of our data. Return to excel workbook and as you can see the range that we are interested in is A2 to B2. Go back to VBA window and type: Customer name equals range parenthesis, double-quotes and A2. Close the quotes and the parenthesis. Enter and type now: CustomerPhone equals range B2. After I select the range in sheet1 I have to go now to sheet 2 to transfer data.
Type in Worksheets sheet2 which is the name of the 2nd worksheet dot select. Now that I’ve selected sheet2 I need to select the exact cell for Customer name. So type in: worksheets sheet2 dot range A1 dot select. There is a problem that I need to solve now. I want always to find the next blank row and then paste the name and phone, so it will not overwrite the previous name and phone. To solve that problem, we need to use a loop with the help of If statement. Let’s get started:
The if statement checks if the row is not empty, go to the last cell and after that go to the next empty cell using activecell statement. So let’s type in:
We will do exactly the same for customer phone. If you want, you can copy and paste the 2 statements and make the appropriate changes. Great! Now we have effectively transferred the data from sheet1 to sheet2. The final step is to go back to sheet1, so we can enter another data. Type : worksheets sheet1 dot select. To clear the data in sheet1 we will write:
Our VBA code is ready and we have to try it , to make sure that it works. So return to sheet1, Disable design mode, type a name and a phone and press the buton. Go to sheet2 and see that the data copied and pasted correctly. Go back to sheet1 and type another name and phone. Click button again and see the results. It works great.
Facebook ► https://www.facebook.com/mellontraining
My Blog ► http://www.mellontraining.com
My Online Excel Courses ► https://www.udemy.com/u/andrewex
Twitter ► https://twitter.com/mellontraining
Linkedin ► gr.linkedin.com/in/andreasexadaktylos
Youtube channel ► http://www.youtube.com/mellontraining
#excelVBA #MsExcel

Видео Transfer Data in Excel From One Worksheet to Another Automatically Using VBA and Macros канала Mellon Training