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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Extract Data from One Sheet to Another Using VBA in Excel


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

how to extract data from one sheet to another in excel using vba


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.

how to extract data from one sheet to another in excel using vba


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

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

  • 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

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

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

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

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.

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

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

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

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.

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

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

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

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

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

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.

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

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.

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

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

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

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


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

<< Go Back To Extract Data Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo