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.


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


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.

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

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 Extract Data from Excel Based on Criteria (5 Ways)


Similar Readings


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

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

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

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

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo