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

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

Here’s an overview of the sample dataset we will be using to illustrate how to extract data from one sheet to another in Excel.

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

Step 1:

Open a Module from the Developer tab by going to,

Developer → Visual Basic

From the Microsoft Visual Basic for Applications – Extract Data from One Sheet to Another window, go to,

Insert → Module

Step 2:

The Extract Data from One Sheet to Another module will pop up. Enter the following VBA:

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

Run the VBA by going to,

Run → Run Sub/UserForm

After running the VBA Code, you will be able to extract data from the sheet named Dataset1 to Dataset2 as shown in the image below.

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

Step 1:

Insert a new module and enter the VBA code:

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

Run the VBA by going to

Run → Run Sub/UserForm

Step 2:

You will be able to extract data from the sheet Dataset1 to Dataset3 as shown in the image below.

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

Step 1:

Insert a new module and enter the following VBA code:

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

Run the VBA code.

Run → Run Sub/UserForm

Step 2:

You will be able to extract data from the sheet named Dataset1 to Dataset4 using the Paste Special command in the VBA code.

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

Learn how to extract data from one workbook to another using VBA.

We will copy the data from the workbook “Extract Data from One Sheet to Another” and the worksheet named “Dataset1”, and paste it into “Sheet1” of our current workbook “Extract Data”.

Step1:

Open 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