Step by Step: How to Write A VBA Code to Copy Data from One Sheet to Another in Excel?

Photo by Wilhelm Gunkel on Unsplash

In previous section, we talked about the definition of VBA and how we can use it in data validation while using Excel for our data. If you haven’t read it before, you can access it via this link.

In this section, we are going to see how we can write a VBA code in Excel that we can copy all data from one sheet to another easily. The beautiful thing about having these kinds of codes that you can apply them to the same kinds of problems when you encounter. Moreover, you can also write automation codes for your daily tasks when you even go further. In a nutshell, we will basically see how to copy our data in this very section.

How to Write A VBA Code to Copy Data from One Sheet to Another?

First, let say that you have a data in your Excel sheet like this;

Excel Sheet with Example Data

There can be hundreds of lines in your sheet but in our example, there are 100 lines including our columns.

Second, you need to open VBA from Developer tab in Excel and write a code to do the job we wanted.

Copy matched columns to a new sheet

Here, you need to be careful in some points. You need to;

1. Be careful about your sheet number (sheet name) when you write your code.
2. Get the total row number from your Original Data sheet (the sheet that you want to copy all data from).
3. Get the total column number from your Original Data sheet.
4. Get the total column number from your Copied Data sheet. (the sheet that you want to copy to).
5. Initialize row and column numbers for your loop use later.
6. Create a loop in order to get your data from your Original Data sheet and copy them to your new sheet (Copied Sheet).

Note: You can also check this blog post and some other interesting topics on my personal web page.

Next: Step by Step: How to Write A VBA Code to Check Valid Cell Values in Excel?