How to Copy Only Highlighted Cells in Excel (3 Quick Tricks)

In this article, we will demonstrate 3 quick ways to copy highlighted cells to a new worksheet in Excel. To illustrate the methods, we’ll use the following dataset that contains some Products, their Delivery Status and Price. The rows that have Delivery Status as No have been highlighted.

Method 1 – Sorting Highlighted Cells Before Copying

Before copying the highlighted cells, we’ll sort the dataset first.

Steps:

Select the dataset that contains the highlighted cells.

Go to the Home tab in the ribbon and select Sort & Filter > Custom Sort.

A Sort box will appear.

In the Sort by field, select Product (the column to be sorted).
In the Sort On field, select Cell Color.
In the Order field, select the color of the highlighted cells and the On Top option.
Click OK.

The highlighted cells are sorted to the top of the data.

Now we can copy the highlighted text to another sheet. There are a variety of methods to do so:

1.1 – Using Keyboard Shortcuts

Steps:

Select the highlighted cells (which are on top after sorting).
Use the keyboard shortcut Ctrl + C to copy the cells.

Go to the destination sheet and select cell B5, the location where the highlighted cells should be pasted.
Use the keyboard shortcut Ctrl + V to paste the copied values.

The highlighted cells are copied to another sheet.

1.2 Using the Context Menu

Steps:

Select the highlighted text.
Right-click and select Copy from the context menu.

Select cell B5 in the sheet where the copied values will be pasted.
Right-click and select Paste from the context menu.

The highlighted cells are copied to a new sheet.

1.3 – Using the Excel Ribbon

Steps:

Select the highlighted dataset as before.
Select Copy > Copy from the Home tab of the ribbon.

Select cell B5 of the sheet where the copied values will be pasted.
Choose the Paste drop-down from the Home tab of the ribbon.
Select Paste.

The highlighted cells are copied to a new worksheet.

1.4 – Using the Paste Special Option

Steps:

Select and copy the highlighted cells as before.
Select the cell (B5) in the sheet where the highlighted cells are to be copied.
Right-click and choose Paste Special from the context menu.

The Paste Special window will appear.

Select All in the Paste field and None in the Operation field.
Click OK.

The highlighted values are copied into a new sheet.

1.5 – Using the Clipboard Option

Steps:

Copy the highlighted cells like in previous methods.

The copied values are stored in the Clipboard.

Go to a new worksheet and select cell B5 where the highlighted cells will be pasted.
Click on the Clipboard icon on the Home tab of the ribbon.
Select the stored data in the Clipboard.

The highlighted cells are copied into a new worksheet.

Method 2 – Copying Non-Adjacent Highlighted Cells Without Sorting

Steps:

Hold down the Ctrl key and select the range of highlighted cells one-by-one.

Use the keyboard shortcut Ctrl + C (or any other method mentioned above) to copy the highlighted cells.
Select cell B5 in a new worksheet and paste the copied values by pressing Ctrl + V (or by using any other method mentioned previously).

The highlighted cells are copied into the new sheet.

Method 3 – Using VBA Code

Steps:

Open the VBA window by pressing Alt + F11.
From VBA Projects, select a worksheet and right-click on it.
Select Insert > Module from the list.

A code window will open.

Enter the following code in it:

Sub CopyHighlightedCells()

Dim Product As Range
Dim ProductCell As Range
Dim HCsheet As Worksheet
Dim HPsheet As Worksheet

Set HCsheet = Worksheets("Original Copy")
Set Product = HCsheet.Range("B5", HCsheet.Range("B5").End(xlDown))
Set HPsheet = Worksheets("Use VBA")


For Each ProductCell In Product

    If ProductCell.Interior.Color = RGB(255, 255, 0) Then
        ProductCell.Resize(1, 4).Copy Destination:= _
            HPsheet.Range("B1").Offset(HPsheet.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
    End If

Next ProductCell

HPsheet.Columns.AutoFit

End Sub

How Does the Code Work?

Sub CopyHighlightedCells()

Dim Product As Range
Dim ProductCell As Range
Dim HCsheet As Worksheet
Dim HPsheet As Worksheet

Set HCsheet = Worksheets("Original Copy")
Set Product = HCsheet.Range("B5", HCsheet.Range("B5").End(xlDown))
Set HPsheet = Worksheets("Use VBA")

We declare 4 variables: Product, ProductCell, HCsheet, and HPsheet.

For Each ProductCell In Product

If ProductCell.Interior.Color = RGB(255, 255, 0) Then

ProductCell.Resize(1, 4).Copy Destination:= _
HPsheet.Range("B1").Offset(HPsheet.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)

End If

Next ProductCell

HPsheet.Columns.AutoFit

End Sub

We run a loop to search for highlighted cells and copy them to a new worksheet.

Click the Run button.

The highlighted cells are copied into a new worksheet.

How to Copy Cell Color in Excel

We can easily copy the color of a cell to another cell in Excel. In the dataset, we’ve highlighted a few cells with color. We will copy this color to another cell.

Steps: