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

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

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 Pull Data from Multiple Worksheets in Excel

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 Get Data from Another Sheet Based on Cell Value in Excel

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

Last but not 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: How to Pull Data from Multiple Worksheets in Excel VBA

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 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’.

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

Download Practice Workbook

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

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

• Extract Filtered Data in Excel to Another Sheet
• How to Pull Values from Another Worksheet in Excel
• Pull Same Cell from Multiple Sheets into Master Column in Excel
• How to Pull Data From Another Sheet Based on Criteria in Excel
• Excel Macro: Extract Data from Multiple Excel Files

<< Go Back To Extract Data Excel | Learn Excel