How to copy data from one sheet to another in Excel using macros

It is very useful and above all developed for multiple uses, you can perform calculations from simple sums to mortgages and others much more complex with keyboard shortcuts that will be very useful. Which is very surprising, because it is difficult to find such a complete tool.

How to copy data from one sheet to another in Excel using macros?

It is extremely important to have a thorough understanding of everything Excel has to offer. In addition to all the functions it has available and which allows you many functions such as using all Excel formulas in a spreadsheet, and another of these many functions is the use of Macros.

Macro is a function that we can enable within Excel. Basically it helps us to automate activities, which we do repeatedly. In short, what a macro does is save a series of actions within Excel and then run them when we want.

We can also say that a macro has the ability to invoke others, this allows us to perform much more complex operations. It is very important to use this function correctly, as it will be very useful when working with Excel.

One of the aids that Macros allows us to do is the ability to copy data from one sheet to another. In this article we will focus on teaching you how to perform this action.

Steps to copy data from one sheet to another in Excel using macros

The first thing we need to do is open Excel and locate the data that we want to copy, we'll call it Sheet 1. So we open or create the sheet in which we want to copy this data, for this explanation we will call it Sheet 2.

Then a we will create a simple macro with the macro recording method, for this we go to the developer tab located at the top of the screen. Let's click and then search where it says Record macros.

Next, we name our Macro and click on accepts, then we mark all the data we want to copy, for example from A1 to D6, we right click and we copy. Let's go to sheet 2 and place the selector in A1 and give enter.

Come on developer and select stop recording. Now let's click Visual basic here we can see the macro we just created, with the name we entered earlier.

If we look at what the macro we just created tells us, we can see that the first thing is that we select from A1 to D6. After copying that selection, on the next line it tells us that we select Sheet 2 and finally paste.

In this way we have created a simple Macro of lines, but we must then optimize it or rather create a better macro using this as a reference. This is so that it works much better.

How to optimize the created macro?

The next thing we're going to do is create an optimized macro in a single line, from Visual Basic. First let's name our macro e we press enter, then we write sheets which will be our collection of sheets.

Now we write (“Sheet1”) after the interval that will be .Range (“A1: D6”) this indicates that we are selecting A1 to D6. So let's write .Copy to copy the data we have selected.

Next, let's write Destinaition: ”sheets (“Sheet2”), with this we indicate that we want it to be copied to Sheet 2. Finally, we write.Range (“A1”) and we mark enter, with this action the copied data will be pasted from box A1.

We will run this macro but in another way, for this we go to the Excel page, then to the tab Developer and we do click your Macro. Now we select our macro created by the name we put on it and click on run.

In this way we have copied successfully the data from Sheet 1 to Sheet 2.

Remember that if you accidentally installed Excel in a language other than Spanish, you can change the language very easily.