Transfer Specific Data from One Worksheet to Another for Reports

While working in Excel we frequently need the necessity to transfer specific data from one worksheet to another for reports in corporate or research works. This task is very important mainly when you are working with a large-size Excel worksheet having lots of data in it. Excel offers some quick and easy methods to do it. I’ll show 5 quick methods in this article with sharp steps and vivid illustrations.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


5 ways to Transfer Specific Data from One Worksheet to Another for Reports

Let’s get introduced to our dataset first. I have placed some salespersons’ sales in different regions in my dataset. Now I’ll use 5 different methods to transfer specific data from one worksheet to another for a report.


Method 1: Automatically Transfer Specific Data from One Excel Worksheet to Another with Copy and Paste Option for Reports

In our very first method, I’ll use the copy and paste option of Excel to send specific data from one worksheet to another for a report.

Steps:

  • Select the data range by using your mouse from your source sheet.
  • Press Ctrl+C on your keyboard and a dancing rectangle will appear.
  • Then go to your destination sheet where you want to send your data.

Transfer Specific Data from One Excel Worksheet to Another with Copy and Paste Option

  • Later, select the cell where you will copy the data.
  • Then right-click your mouse.
  • Select the Copy Link option from the Paste Options in the context menu.

Transfer Specific Data from One Excel Worksheet to Another with Copy and Paste Option

Now see that the data is transferred. But you will not get the previous data format. So you will have to format it again.

Transfer Specific Data from One Excel Worksheet to Another with Copy and Paste Option

If we change any data in the source sheet then it will be changed automatically in the destination sheet too.

Here, I have removed the first sales data.

Now take a look in the destination sheet that here the data is removed too.

Read More: How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)


Method 2: Automatically Send Specific Data from One Excel Worksheet to Another for Reports by Entering the Formula Manually

We can enter a formula manually to transfer specific data from one worksheet to another for a report. But the limitation of this method is that it can only transfer specific cells’ data to another sheet. In the source dataset, I have calculated the total sales in Cell D13. Now I’ll transfer it to another sheet. Using formula.

Send Specific Data from One Excel Worksheet to Another by Entering the Formula Manually

Steps:

  • Go to your destination sheet.
  • Type = in Cell D4.

  • After that go to the source sheet and select the cell where your data is located.
  • Then just press the Enter

And you will get the transferred value in your destination sheet.

Read More: How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)


Method 3: Transfer Specific Data to Another Sheet by Using Move Or Copy Sheet Command in Excel for Reports

Here, we’ll apply another Excel option which is the Move or Copy Sheet command. This command is located in the Format option in Excel.

Steps:

  • Go to that sheet that you want to transfer or copy.
  • Then click as follows-

Home > Format > Move or Copy Sheet.

A dialog box will open up.

Transfer Specific Data To Another Sheet by Using Move Or Copy Sheet Command In Excel

  • Select your sheet from the Before Sheet
  • Put a mark on Create a copy.
  • Click the OK

Now take a look that the whole sheet is copied with a serial number before the source sheet.

Read More: How to reference cell in another Excel sheet based on cell value!


Related Readings


Method 4: Send Specific Data From One Excel Worksheet to Another for Reports Using Macros

Now we’ll use VBA Macros to do the operation smartly and it’s pretty fast. For that, I have opened two new sheets. The sheet named ‘VBA’ contains the data we will transfer and the sheet named ‘VBA-Destination’ is the destination sheet. We’ll have to use these names in the codes.

Send Specific Data from One Excel Sheet to Another Using Macros

Steps:

  • Right-click on the source sheet title.
  • Select the View Code option from the context menu.

A VBA window will appear.

Send Specific Data from One Excel Sheet to Another Using Macros

  • Then write the following codes in it-
Sub TransferDataToAnotherSheet()
 Sheets("VBA").Range("B4:D11").Copy Destination:=Sheets("VBA-Destination").Range("B4")
End Sub
  • Later, click the Run icon to run the codes. Soon after a Macro dialog box will open up.

Send Specific Data from One Excel Sheet to Another Using Macros

  • Select the macro name that we mentioned in the codes.
  • Finally, just press Run.

Now the specific data is transferred to the destination sheet with the original format.

Read More: How to Link Data in Excel from One Sheet to Another (4 Ways)


Method 5: Transfer Specific Data from One Excel Worksheet to Another Using Macros in Different Workbooks

In our last method, I’ll show how to transfer specific data from one sheet to another in different workbooks. The workbook given below is my source workbook. And I have opened a new workbook to send data there.

Transfer Specific Data from One Excel Sheet to Another Using Macros in Different Workbooks

This workbook is my destination workbook. I will send data here from the source workbook.

Transfer Specific Data from One Excel Sheet to Another Using Macros in Different Workbooks

Steps:

  • Right-click on the source sheet title.
  • From the context menu select View Code.

  • After appearing the VBA window write down the following codes in it-
Sub TransferData_Another_Workbooks()
   Workbooks("Transfer Specific Data from One Worksheet to Another for Report.xlsm").Worksheets("VBA-Source").Range("B2:D11").Copy _
   Workbooks("Destination.xlsm").Worksheets("Sheet1").Range("B2")
End Sub
  • Finally, just click the Run icon to run the written codes.

Now you will spot that the specific data is transferred to another sheet in the destination workbook.

Read More: Reference from Another Excel Workbook without Opening (5 Examples)


Conclusion

I hope the procedures described above will be good enough to transfer specific data from one worksheet to another for reports. Feel free to ask any question in the comment section and please give me feedback.


Further Readings

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo