Learn how to copy data from one Excel sheet to another by following along with this effortless Excel tutorial.

Ever found yourself staring at an Excel spreadsheet, wishing you could just snip and paste data from one sheet to another? Well, you can!

Copying data between Excel sheets is a handy skill that can save you time and effort. In this guide, I’ll break down the process into simple steps, so you can become an Excel data-moving master. Let’s get started!

Copy-Paste With Keyboard Shortcuts

The easiest way to copy Excel data from one sheet to another is the copy-and-paste method.

Here, you can use different ways and shortcut keys to copy an Excel worksheet in various ways.

Select the first cell, A1 in the worksheet and press Ctrl + A to copy the entire dataset as shown in the above image.

In this approach, Excel first selects all of the contiguous rows and columns of the dataset. Nonadjacent cell ranges or the same worksheet’s datasets won’t be copied first.

Suppose, your worksheet looks something like the above.

To select all the data from the active worksheet, press Ctrl + A twice.

Excel will select the entire worksheet.

Another way to select a certain part is to use the Ctrl + Shift + Right Arrow or Down Arrow hotkey.

It selects the entire row or column respectively until the next empty cell.

Once you’ve copied the source dataset from the active worksheet, navigate to the destination sheet. It might be in the same workbook or a different one.

Select the first cell of the destination cell range and press Ctrl + V to paste the copied data. Here, you don’t need to select the entire destination cell range because that would unnecessarily take more time. Excel will automatically adjust the pasted data.

For instance, you’ve copied source data from the A1:I6 cell range. Now, you want to paste it in the K1:S6 cell range. You only need to select K1 in this situation.

Drag and Drop Data Using the New Window Command

In this method, you can visualize both the source and destination worksheet on one screen. Also, you can use the drag-and-drop movement to copy Excel data from one sheet to another.

Go to the source data worksheet and click on the View tab. Find the New Window command inside the Window commands block. Click on that to open a new window of the same workbook.

You’ll see the new window on the top. The active worksheet would be the source sheet. So, navigate to the destination worksheet in the new window.

Click anywhere on the sheet and press the Windows + Right Arrow keys together to snap the new window to the right half of the screen.

Now, you should see the source data sheet. Select any cell on that and press the Windows + Left Arrow hotkey to snap the window to the left half.

Both the worksheets will be in front of you.

Select a dataset in the source worksheet and hit the Ctrl key.

Now, drag and drop it onto the destination worksheet on the right side of the screen.

Excel copies data when you drop the selected dataset.

If you need to copy to a worksheet of a different Excel file, you can just open that worksheet and press Windows + Right Arrow to snap to the right. No need to use the New Window command in this case.

Copy Data Using the Paste Link Command

The Paste Link option in Excel is used to create a dynamic link between cells or data across different worksheets or workbooks. When you paste a link, the destination range references the original cell range, meaning any changes made to the source data automatically update in the linked cells.

To use Paste Link, go to the source worksheet and copy the intended dataset.

Now, navigate to the destination worksheet, which can be the same workbook or a different Excel file.

Select a cell or cell range and press Ctrl + Alt + V to open the Paste Special dialog box.

Click on the Paste Link button in the bottom left corner of the dialog box.

Click OK to insert the copied data as a dynamic link that updates in real time.

Excel pastes the data in the destination.

If you’ve copied the dataset to a different workbook, both the Excel files must be open so that Excel can update the dataset dynamically.

Using an Excel Formula

You can also copy datasets from one worksheet to another using an Excel formula that creates a reference of the copied data in the destination cell range. Like the Paste Link method, data updates dynamically when you change the source worksheet content.

For example, you want to copy the dataset in D1:F6 of the database shown above to A1:B6 of Sheet2 in the same workbook.

Go to the destination worksheet and select the first cell of the destination range.

There, enter the following formula and hit Enter:

=Sheet3!$D$1:$F$6

Excel will create a dynamic link in A1:B6 of the copied cell range from the source sheet.

If the destination worksheet is on a different workbook, use the following formula instead:

='[Copy Data From One Excel Sheet to Another.xlsx]Sheet3'!$D$1:$F$6

In the above formula, the codes within single quotes represent the name of the source data workbook and worksheet. So, modify this part as well as the cell range reference.

📒 Read More: 5 Ways to Pull Data From Another Sheet in Microsoft Excel

Copy Data Using a Defined Name

Select the source dataset and navigate to the Defined Names block of the Functions tab.

Click on the Define Name command to view the New Name dialog box.

Use the New Name wizard to define a name for the dataset by entering relevant information in fields, like Name, Scope, and Comment.

The Refers to field should automatically show the selected dataset’s cell range.

Click OK to save the name.

Now, go to the destination worksheet and select the cell as the anchor cell to copy the source data.

Enter the equals sign and type the Defined Name that you’ve just created.

Excel will show automated suggestions if you type the initials. Click on the Defined Name that populates in the auto-suggestion.

Excel will copy and paste the source data into the intended cell range.

However, if you change the source data, the dataset in the destination cell range will reflect that in real-time.

Using the Excel Table Tool

Transform your dataset into an Excel table by selecting it and pressing the Ctrl + T keys together.

Click OK on the Create Table dialog box.

Excel will convert the data into a table.

Click anywhere on the table and go to the Table Design tab.

Note the Table Name entry in the Properties block.

Now, go to the destination worksheet and select a cell you want to use the anchor.

Type in an equals sign and enter the table name as is.

Select the table object of the same name from the auto-suggestion context menu and hit Enter.

The source table will show up in the destination worksheet with the dynamic linking feature.

If you change the source table data, it’ll reflect in the destination worksheet.

Copy Data Using Excel VBA

If you wish to automate the process of copying and pasting data from one worksheet to another, you can use Excel VBA.

In this section, I’ve outlined two VBA scripts for copying data to a worksheet of the same workbook and for a sheet in a different workbook.

If you’re new to Excel VBA, check out this Microsoft Excel tutorial to find out the technique to create a VBA macro from a script:

📒 Read More: How To Use The VBA Code You Find Online

If you know how to set up a VBA macro or have gone through the above tutorial, try out the following scripts:

Sub CopyRangeToAnotherSheet()
    Dim sourceRange As Range
    Dim destSheetName As String
    Dim destAddress As String
    Dim destSheet As Worksheet
    Dim fullAddress As Variant

    ' Prompt to select the source range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the range to copy using your mouse:", Type:=8)
    On Error GoTo 0
    
    If sourceRange Is Nothing Then
        MsgBox "No range selected. Exiting."
        Exit Sub
    End If

    ' Prompt to enter the destination sheet and address
    destAddress = InputBox("Enter the destination sheet name and cell address in this format Sheet6!A1:")

    If destAddress = "" Then
        MsgBox "No destination address entered. Exiting."
        Exit Sub
    End If

    ' Split the input into sheet name and cell address
    fullAddress = Split(destAddress, "!")
    
    If UBound(fullAddress) <> 1 Then
        MsgBox "Invalid format. Please enter in the format: SheetName!Address (e.g., Sheet6!A1). Exiting."
        Exit Sub
    End If
    
    On Error Resume Next
    Set destSheet = ThisWorkbook.Sheets(fullAddress(0))
    On Error GoTo 0
    
    If destSheet Is Nothing Then
        MsgBox "Sheet " & fullAddress(0) & " does not exist. Exiting."
        Exit Sub
    End If
    
    ' Copy the range to the specified destination
    sourceRange.Copy Destination:=destSheet.Range(fullAddress(1))

    MsgBox "Data copied successfully!"
End Sub

When you’re done setting up the macro, press Alt + F8 to launch the Macro dialog box.

Select the CopyRangeToAnotherSheet macro and hit Run.

Use the input box to choose the source dataset from the active worksheet.

Now, enter the destination worksheet and anchor cell’s reference in this format:

Sheet6!A1

The VBA macro will complete the data copying process and show a confirmation dialog box.

If you need to copy data to a worksheet in a different Excel file, use this script instead:

Sub CopyRangeToAnotherWorkbook()
    Dim srcRange As Range
    Dim destWorkbook As Workbook
    Dim destSheet As Worksheet
    Dim destAddress As String
    Dim filePath As String
    Dim sheetName As String
    Dim cellAddress As String
    Dim splitAddress() As String

    ' Step 1: Get the range to copy
    On Error Resume Next
    Set srcRange = Application.InputBox("Select the range to copy", Type:=8)
    On Error GoTo 0

    If srcRange Is Nothing Then
        MsgBox "No range selected. Exiting."
        Exit Sub
    End If

    ' Step 2: Open file explorer to choose the destination workbook
    filePath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", Title:="Select the destination workbook")
    
    If filePath = "False" Then
        MsgBox "No file selected. Exiting."
        Exit Sub
    End If

    ' Open the destination workbook
    Set destWorkbook = Workbooks.Open(filePath)

    ' Step 3: Get the destination sheet name and cell address
    destAddress = InputBox("Enter the destination in the format SheetName!CellAddress (e.g., Sheet6!A1):", "Destination Address")

    If destAddress = "" Then
        MsgBox "No destination address entered. Exiting."
        Exit Sub
    End If

    ' Split the address into sheet name and cell address
    splitAddress = Split(destAddress, "!")
    If UBound(splitAddress) <> 1 Then
        MsgBox "Invalid format. Please enter in the format SheetName!CellAddress (e.g., Sheet6!A1). Exiting."
        Exit Sub
    End If

    sheetName = splitAddress(0)
    cellAddress = splitAddress(1)

    ' Check if the sheet exists in the destination workbook
    On Error Resume Next
    Set destSheet = destWorkbook.Sheets(sheetName)
    On Error GoTo 0

    If destSheet Is Nothing Then
        MsgBox "The specified sheet does not exist in the destination workbook. Exiting."
        destWorkbook.Close SaveChanges:=False
        Exit Sub
    End If

    ' Step 4: Copy the range to the destination
    srcRange.Copy Destination:=destSheet.Range(cellAddress)

    ' Optional: Clean up
    destWorkbook.Save
    MsgBox "Data copied successfully!"

    ' Close the destination workbook
    destWorkbook.Close SaveChanges:=True
End Sub

When you execute this script, Excel VBA will show a file browsing dialog box so you can choose the destination workbook on your PC and launch the file.

Once the file is open, use the worksheet reference dialog box to enter the destination sheet name and cell address in the following format:

Sheet6!A1

The VBA macro will show a confirmation if the process is successful.

Copy Data Using Power Automate

Suppose, you get fresh data in the form of Excel workbooks from a business app, let’s say a sales CRM. The workbook contains updated data for each day. You’d like to collate regular sales data in a monthly sales workbook.

Instead of manually accessing each day’s data and copying that to the monthly sales file each day, you can set up a Power Automate flow that’ll utilize Office Scripts. The automation flow will access the fresh data workbook, copy its content, and paste it into the last row of the main workbook where you’re collating sales data.

📝 Note: You’ll need a paid subscription to Microsoft 365, preferably Business Standard or better to access all the tools and functionalities used in this method.

Create a Directory on OneDrive Business

Open OneDrive for Business portal and create a directory named Daily Sales.

You can get a URL for this folder and feed that into your sales CRM. The software will create and save daily sales reports in this directory automatically.

Create Required XLSX Files

You’ll also need to create an Excel workbook in OneDrive for Business where you’ll save Office Scripts and let Power Automate aggregate daily sales data automatically.

For this, you can create a Monthly Sales directory in OneDrive. Inside this folder, create the Monthly Sales XLSX file.

The central Excel file, Monthly Sales, should have the same columns as the sales CRM tool generates in the Daily Sales XLSX file.

For this tutorial, I’ve created one as shown above.

Make sure to rename the worksheet to Data.

This is the same data structure that I’ve got in the Daily Sales Excel workbook.

Don’t keep the Daily Sales file inside the Daily Sales directory yet. This file will be used as a trigger for the automation.

Create and Save Office Scripts Codes

Open the Monthly Sales workbook and navigate to the Automate tab.

Click on the New Script command to bring up the Code Editor panel.

Inside the coding interface, enter the following Office Scripts code and save it as Fetch Data from Excel.

function main(workbook: ExcelScript.Workbook, 
  wsName: string, startCell: string, 
  headerRowsToRemove: number, 
  footerRowsToRemove: number) {

  //Declare and assign the worksheet
  let ws = workbook.getWorksheet(wsName);

  //Delare and assign the data range
  let wsRng = ws.getRange(startCell).
    getSurroundingRegion()

  //Get the values of the data range in array
  let wsRngArr = wsRng.getValues();

  //Count the rows in array
  let wsRngArrRowCount = wsRngArr.length;

  //Calcualte the number of rows to return
  let wsRngArrRowLength = wsRngArrRowCount - 
    headerRowsToRemove - footerRowsToRemove + 1;

  //Remove the top and bottom rows
  wsRngArr = wsRngArr.slice(headerRowsToRemove, wsRngArrRowLength)

  //Return the range
  return { wsRngArr };

}

Now, repeat the same steps and create another Office Scripts code as Paste Data to Excel from PA:

function main(workbook: ExcelScript.Workbook, wsName: string,
    startCell: string, strArr: string) {

    //Convert the strArr to an array
    let newDataArr: string[][] = JSON.parse(strArr)

    //Declare and assign the worksheet
    let ws = workbook.getWorksheet(wsName);

    //Get the old data
    let dataArr = ws.getRange(startCell).getSurroundingRegion().
        getValues()

    //Join the dataArr and newDataArr
    dataArr = dataArr.concat(newDataArr)

    //Get the size of the range to paste to
    let dataRng = ws.getRange(startCell).
        getAbsoluteResizedRange(dataArr.length, dataArr[0].length)

    //Set the values of the new data set
    dataRng.setValues(dataArr)

}

Set Up the Power Automate Flow

Open Power Automate, navigate to the Create menu and click on the Automated cloud flow.

The Build an automated cloud flow dialog will open. There, enter a name in the Flow name field.

In the Search a trigger field, enter File created and choose When a file is created Onedrive for Business trigger.

Click Create to enter the flow editing canvas.

In the flow-editing canvas, click on the trigger and set Daily Sales as the default folder using the Parameters field.

Click the plus icon and select Add an action.

On the left side navigation panel, look up Run script and select Excel Online (Business) Run script action.

Set up the Parameters for this action as shown in the screenshot above.

Follow the previous steps again to add the second Run script action and populate values in the Parameters field as illustrated in the image.

Hit the Save button on the top right corner.

Executing the Automation

To run the flow, drag and drop the Daily Sales XLSX file into the Daily Sales directory of OneDrive for Business.

The Power Automate flow will run and show a status message as you can see in the screenshot above.

You’ll also see new data in the Monthly Sales Excel workbook.

Conclusions

Now you know how to copy data from one Excel sheet to another using various user interface commands, formulas, and programmatic methods.

If you liked the Excel tutorial, you can acknowledge it by commenting below. Also, share any suggestions you might have after trying these techniques.