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

Highlighting cells is quite useful while working on the Excel worksheet. The highlighted cells can be spread out through the sheet and we may want to copy the highlighted cells to another sheet for better analysis. In this article, I will show you 3 quick tricks to copy only highlighted cells in Excel.

How to Copy Only Highlighted Cells in Excel: 3 Quick Tricks

Highlighted cells can be copied in several ways. Here, I will show 3 quick tricks to copy highlighted cells to another sheet in Excel. To demonstrate the procedure, I have taken a dataset that contains the Product, its Delivery Status and Price. I have highlighted the rows that have Delivery Status as No.

1. Sorting Highlighted Cells for Copying in Excel

Before copying the highlighted cells, we need to sort the highlighted cells first. I will show here the steps for sorting highlighted cells. After that, we will discuss the procedures to copy these sorted cells in Excel.

First, select the dataset that contains the highlighted cells.

Now, 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 on which sorting will occur), in the Sort On field select Cell Color, in the Order field select the color of highlighted cells and choose the On Top option.
Finally, press OK.

Sorting is done. We will see the highlighted cells on top of the data.

1.1 Use Keyboard Shortcut

Now it’s time to copy the highlighted text to another sheet. We can do that by using the keyboard shortcut. Please follow the steps below to copy the highlighted cells.

First, select the highlighted cells which are on top after sorting.
Now, use the keyboard shortcut Ctrl + C to copy the cells.

Then, go to the sheet and select cell B5 where the highlighted cells should be pasted.
Afterward, use the keyboard shortcut Ctrl + V to paste the copied values.
Finally, the highlighted cells are copied to another sheet.

1.2 Apply Context Menu

We can copy the highlighted cells by applying the context menu. Let’s follow the steps given below.

First, select the highlighted text.
Then, right-click and select Copy from the option in the context menu.

Now, select cell B5 in a new sheet where we will paste the copied values.
Further, right-click and select Paste from the option.

Finally, we will see the highlighted cells copied in a new sheet.

1.3 Use Excel Ribbon

Another way to copy the highlighted cells is to use the Copy option from the Excel ribbon. I am showing the steps below for this method to copy.

First, select the highlighted dataset as before.
Then, select Copy > Copy from the Home tab of the ribbon.

Now, select cell B5 of a new sheet where we want our copied values pasted.
After that, choose the Paste drop-down from the Home tab of the ribbon.
Select Paste.

Hurrah! We can see the highlighted cells copied to a new worksheet.

1.4 Utilize Paste Special Option

We can also utilize the Paste Special option of Excel to copy the highlighted cells. To do that, please follow the steps given below.

Firstly, select and copy the highlighted cells as before.
Afterward, select the cell (B5) of a new sheet where we wish to copy the highlighted cells.
Then, right-click and choose Paste Special from the options.

Paste Special window will appear, select All in the Paste field and select None in the Operation field.
Finally, press OK.

Consequently, we will see the highlighted values copied in a new sheet.

1.5 Apply Clipboard Option

Another way of copying highlighted cells can be done by applying the Clipboard option. Please, follow the steps given below to copy highlighted cells by applying the clipboard option.

Firstly, copy the highlighted cells like in previous methods.
The copied values are already stored in the Clipboard.
Now, go to a new worksheet and select cell B5 where we want to copy the highlighted cells.
After that, click on the Clipboard icon in the Home tab of the ribbon.
Further, select the stored data in the Clipboard.

Bravo! The highlighted cells are copied in a new worksheet.

2. Copy Non-Adjacent Highlighted Cells Without Sorting

There’s another way to copy the highlighted cells without sorting them. The procedure is described below stepwise.

First, hold the Ctrl key and select the range of highlighted cells separately.

Next, use the keyboard shortcut Ctrl + C to copy the highlighted cells. You can use any other method mentioned above to copy the cells.
Now, select cell B5 in a new worksheet and paste the copied values by pressing Ctrl + V or by using any other method mentioned previously.
Yahoo! We can see the highlighted cells copied in a new sheet.

3. Insert VBA to Copy Highlighted Cells to Another Sheet

We can use a simple Visual Basic for Applications (VBA) code to copy the highlighted cells in a new worksheet. Let’s follow the steps given below for this quick trick.

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

A code window will show up. Write the following code there.

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?

The code is explained below partwise.

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")

In this section, we have declared 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

And this section runs a loop to search for highlighted cells and copy them to a new worksheet.

After that, press Run from the VBA window.

Finally, we can see the highlighted cells are copied in a new worksheet.

How to Copy Cell Color in Excel

We can easily copy the color of a cell to another cell in Excel. Here, I will show the method to copy the color of a cell to another cell. In the dataset, I have introduced Product, Delivery Status, and Price and highlighted a few cells with color. We will copy the color to another cell. I am showing the steps for this procedure below.

First, select the cell (B5) whose color we will copy.
Then, go to the Home tab in the ribbon and select Format Painter.

Consequently, we copied the cell color already.
Finally, go to the desired sheet and select the cell whose color you want to change. In my case, the cell is C5.
Bravo! We copied the cell color successfully.

Download Practice Workbook

You can download the practice workbook from here and practice.

Copy Only Highlighted Cells.xlsm

Conclusion

Copying highlighted cells from a dataset comes in handy sometimes. In this article, I have shown 3 quick tricks to copy highlighted cells in Excel. If you have any queries, please let us know by commenting.

<< Go Back to Copy a Cell | Copy Paste in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!