Md. Sourov Hossain Mithun
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.
- 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.
Now see that the data is transferred. But you will not get the previous data format. So you will have to format it again.
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.
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.
- 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
- How to Link Excel Sheets to Another Sheet (5 Ways)
- Link Excel Workbooks for Automatic Update (5 Methods)
- How to Link Files in Excel (5 Different Approaches)
- Link Word Document to Excel (2 Easy Methods)
- How to Link Cell to Another Sheet in Excel (7 Methods)
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.
Steps:
- Right-click on the source sheet title.
- Select the View Code option from the context menu.
A VBA window will appear.
- 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.
- 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.
This workbook is my destination workbook. I will send data here from the source workbook.
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.
