How to copy only borders of selected range in Excel?
When working in Microsoft Excel, you often need to replicate specific formatting from one range to another. Standard copy-paste operations allow you to copy cell values, formulas, or even overall cell formatting using the Paste Special feature. However, situations may arise where you only want to replicate the border style of a selected range, without transferring other formats like colors, fonts, or fill patterns. For example, you might want to maintain a consistent table border design across multiple sections of your worksheet or mirror border layouts in new template sheets. Unfortunately, Excel does not provide a straightforward, built-in option to copy and paste only cell borders. This tutorial will walk you through a practical method to copy just the borders of any selected range and apply them to a new range in Excel, ensuring formatting consistency without disrupting other cell properties.
Use VBA code to only copy borders of selected range
Alternative solution: Manually replicate borders using Format Painter
Use VBA code to only copy borders of selected range
Using a simple VBA macro, you can efficiently copy the exact border style from one cell or range and apply it to another location in your worksheet, regardless of the content or other formatting attributes. This solution is ideal when you need automation for repetitive tasks or want to speed up your workflow when handling custom-styled tables and templates. Please follow the detailed steps below to safely run the VBA code and notice useful parameter tips along the way.
1. Press the Alt + F11 keys simultaneously on your keyboard. This will open the Microsoft Visual Basic for Applications (VBA) editor window. If you don’t see the Developer tab in Excel, just use the shortcut combination, as it opens the VBA editor directly in any modern Excel version.
2. In the VBA editor, click Insert on the menu bar, then select Module. A new blank module window will open. Copy and paste the following VBA code into this window.
VBA code: Copy only borders of selected range in Excel
Sub CopyBorders()
'Updated by Extendoffice 20211203
Dim xRg, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox("Select Range with Borders to Copy...", "Kutools For Excel", , , , , , 8)
Set yRg = Application.InputBox("Select Cell to Apply Borders to range..", "Kutools For Excel", , , , , , 8)
With yRg.Borders(xlEdgeLeft)
.LineStyle = xRg.Borders(xlEdgeLeft).LineStyle
.ColorIndex = xRg.Borders(xlEdgeLeft).ColorIndex
.TintAndShade = xRg.Borders(xlEdgeLeft).TintAndShade
.Weight = xRg.Borders(xlEdgeLeft).Weight
End With
With yRg.Borders(xlEdgeTop)
.LineStyle = xRg.Borders(xlEdgeTop).LineStyle
.ColorIndex = xRg.Borders(xlEdgeTop).ColorIndex
.TintAndShade = xRg.Borders(xlEdgeTop).TintAndShade
.Weight = xRg.Borders(xlEdgeTop).Weight
End With
With yRg.Borders(xlEdgeBottom)
.LineStyle = xRg.Borders(xlEdgeBottom).LineStyle
.ColorIndex = xRg.Borders(xlEdgeBottom).ColorIndex
.TintAndShade = xRg.Borders(xlEdgeBottom).TintAndShade
.Weight = xRg.Borders(xlEdgeBottom).Weight
End With
With yRg.Borders(xlEdgeRight)
.LineStyle = xRg.Borders(xlEdgeRight).LineStyle
.ColorIndex = xRg.Borders(xlEdgeRight).ColorIndex
.TintAndShade = xRg.Borders(xlEdgeRight).TintAndShade
.Weight = xRg.Borders(xlEdgeRight).Weight
End With
With yRg.Borders(xlInsideHorizontal)
.LineStyle = xRg.Borders(xlInsideHorizontal).LineStyle
.ColorIndex = xRg.Borders(xlInsideHorizontal).ColorIndex
.TintAndShade = xRg.Borders(xlInsideHorizontal).TintAndShade
.Weight = xRg.Borders(xlInsideHorizontal).Weight
End With
With yRg.Borders(xlInsideVertical)
.LineStyle = xRg.Borders(xlInsideVertical).LineStyle
.ColorIndex = xRg.Borders(xlInsideVertical).ColorIndex
.TintAndShade = xRg.Borders(xlInsideVertical).TintAndShade
.Weight = xRg.Borders(xlInsideVertical).Weight
End With
End Sub
3. After pasting the code, you can run the macro by pressing the F5 key or by selecting Run from the VBA editor toolbar. When prompted in the first "Kutools for Excel" dialog box, use your mouse to highlight the range containing the borders you want to replicate, then confirm by clicking "OK".
4. A second dialog box titled "Kutools for Excel" will appear. Now, select the top-left cell of the destination range where you would like the copied border style to be applied. Once you have chosen your destination cell and possibly a range, click "OK". See illustration below:
After completing these steps, the border design of the original range is now applied to the new location you selected. Only the border formatting transfers; cell data, colors, and other features remain unaffected. This is useful when creating uniform tables or copying worksheet aesthetics. A sample result is shown below:
Alternative solution: Manually replicate borders using Format Painter
For users who want a no-code approach to copying borders, Excel’s built-in Format Painter tool offers a quick way to duplicate border formatting from one range to another, along with some other formatting styles. Here’s how you can use it:
- Select the range that contains the borders you want to copy.
- On the Home tab, in the Clipboard group, click the Format Painter (paintbrush icon).
- Your cursor turns into a paintbrush. Select the destination range where you want the borders to appear. This will apply the original range’s borders and some other formats (such as font and fill color) together.
Pros: Simple and built-in to Excel; suitable for quick, visual copying tasks.
Cons: Cannot copy only the border style (also copies other cell formats like fill color, font changes, etc.). Not ideal if you want to keep target cell content and formatting unchanged except for borders.
Each method comes with its own specific strengths and is suited for different workflow needs: VBA offers precise border-only transfer, Format Painter facilitates fast all-format copying.
Related articles:
- How to copy and paste values skipping duplicates in Excel?
- How to copy cells data with row height and column width in Excel?
- How to copy cell as text value not formula in Excel?
- How to copy data to next empty row of another worksheet in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in