How to Only Copy Formatting from a Range to Another in Excel?

Excel is a robust spreadsheet programme that provides a wide range of tools to assist you in manipulating and analysing data. Making your data readable and visually appealing requires careful formatting. The task of copying formatting from a particular range of cells and applying it to another range will be the main emphasis of this course. When you need to copy formatting from one table to another or when you want to keep your worksheet's formatting constant throughout, doing so can help you save a lot of time.

This article will give you detailed instructions and real-world examples to help you master the skill of copying formatting in Excel, regardless of your level of Excel proficiency. So let's get started and discover how to prepare your data quickly and easily!

Only Copy Formatting from a Range to Another

Here we will first create a VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can only copy formatting from one range to another in Excel.

Step 1

Consider an Excel sheet where you have formatted cells.

First, right-click on the sheet name and select View code to open the VBA application.

Right Click > View Code.

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Code

Sub CopyFormat()
Dim CopyRng As Range, PasteRng As Range
xTitleId = "Only Copy Formatting"
Set CopyRng = Application.Selection
Set CopyRng = Application.InputBox("Ranges to be copied :", xTitleId, CopyRng.Address, Type:=8)
Set PasteRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
CopyRng.Copy
PasteRng.Parent.Activate
PasteRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Step 3

Then click F5 to run the module. Then select the range of source cells and click OK.

F5 > Select Cells > Ok.

Step 4

Then select a single cell where you need to place the comments and click OK.

Select cell > Ok.

This is how you can only copy format from one range to another in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can only copy formatting from one range to another in Excel to highlight a particular set of data.