Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)

While working with large Microsoft Excel, sometimes we need to extract data from one sheet to another for the convenience of our work. We can easily do that by using the VBA Macros. Applying a VBA code to extract data from one sheet to another is an easy task. Today, in this article, we’ll learn three quick and suitable ways to extract data from one sheet to another in Excel using VBA code effectively with appropriate illustrations.

Excel VBA to Extract Data from One Sheet to Another (Quick View)

Sub Extract_Data_from_One_Sheet_to_Another()
Sheets("Dataset1").Range("B2:D14").Copy
Sheets("Dataset3").Range("B2:D14").PasteSpecial
End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

3 Suitable Ways to Extract Data from One Sheet to Another Using VBA in Excel

Let’s say, we have a dataset that contains information about several Students of Armani College. The Name of the Students, their Identification Number, and their CGPA are given in columns B, C, and D respectively. From our dataset, we will extract data from one sheet to another in Excel using VBA Code. To do that, we will apply the Copy Paste Command, Paste Special Command, and so on in VBA Code. Here’s an overview of the dataset for today’s task.

1. Apply the Mathematical Operator to Extract Data from One Sheet to Another with Excel VBA

Now I’ll show how to extract data from one sheet to another in Excel using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will extract data from the sheet named Dataset1 to Dataset2 using VBA code with the mathematical operator. Let’s follow the instructions below to extract data from the sheet named Dataset1 to Dataset2!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Extract Data from One Sheet to Another will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Extract Data from One Sheet to Another module pops up. In the Extract Data from One Sheet to Another module, write down the below VBA

Sub Extract_Data_from_One_Sheet_to_Another()
Sheets("Dataset1").Range("B2:D16").Copy Destination:=Sheets("Dataset2").Range("B2")
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, you will be able to extract data from the sheet named Dataset1 to Dataset2 which has been given in the below screenshot.

Read More: How to Extract Data from Excel Sheet (6 Effective Methods)

2. Use the Copy Paste Command in VBA Code to Extract Data from One Sheet to Another in Excel

Now, from our dataset, we will extract data from the sheet named Dataset1 to Dataset3 using the Copy-Paste command in VBA code. Undoubtedly, this is an easy and time-saving task.

Let’s follow the steps below to extract data!

Step 1:

• According to method 1, insert a new module and type the below VBA code to extract data from the sheet named Dataset1 to Dataset3 using the Copy-Paste command in the VBA code. The VBA code is,

Sub Extract_Data()
Sheets("Dataset1").Range("B2:D16").Copy
Sheets("Dataset3").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• While running the code, you will be able to extract data from the sheet named Dataset1 to Dataset3 which has been given in the below screenshot.

Read More: How to Extract Data from Excel Based on Criteria (5 Ways)

Similar Readings

• How to Extract Year from Date in Excel (3 Ways)
• How to Extract Month from Date in Excel (5 Quick Ways)
• Extract Text After a Character in Excel (6 Ways)
• Excel Formula to Get First 3 Characters from a Cell(6 Ways)
• How to Pull Data From Another Sheet Based on Criteria in Excel

3. Apply the Paste Special Command in VBA Code to Extract Data from One Sheet to Another in Excel

Last but not the least, you can also extract data from the sheet named Dataset1 to Dataset4 using the Paste Special command in VBA code. This is an easy task and time-saving also. Let’s follow the instructions below to extract data from the sheet named Dataset1 to Dataset4 using the Paste Special command in VBA code!

Step 1:

• First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,

Sub Use_Paste_Special_to_Extract_Data()
Sheets("Dataset1").Range("B2:D16").Copy
Sheets("Dataset4").Range("B2:D16").PasteSpecial
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• After running the VBA Code, you will be able to extract data from the sheet named Dataset1 to Dataset4 using the Paste Special command in the VBA code which has been given in the below screenshot.

Read More: Transfer Data from One Excel Worksheet to Another Automatically

Bonus: Extract Data from One Workbook to Another Using VBA in Excel

It is a very important section, as we will extract data from one workbook to another. We will copy the data from the workbook “Extract Data from One Sheet to Another” and the worksheet named “Dataset1”. Then, we will paste it into “Sheet1” of our current workbook “Extract Data”. Follow the steps below to have done the extract data.

Step1:

• Select a new Module and paste the following VBA.

Sub Extract_Data_from_Another_Workbook()
Workbooks("Extract Data from One Sheet to Another.xlsm").Worksheets("Dataset1").Range("B2:D16").Copy
Sheets("Sheet1").Range("B2:D16").PasteSpecial
End Sub

Step 2:

• To run the program, press F5 after saving.

• As a result, you will see the copied cells from another workbook.

• Besides, the pasted cells in the current workbook in ‘Sheet1’.

Read More: How to Transfer Data from One Sheet to another in Excel Using Macros

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Conclusion

I hope all of the suitable methods mentioned above to extract data from one sheet to another with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Extract Data From Table Based on Multiple Criteria in Excel
• Return Multiple Values in Excel Based on Single Criteria (3 Options)
• Import Data from Excel into Word Automatically Using VBA (2 Ways)
• How to Extract Data from a List Using Excel Formula (5 Methods)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Data from Cell in Excel (5 Methods)