'PasteSpecial that removes cell color / Format but keeps the text format

What I'm trying to do is copying cells that have a cell format (like color and borders) and text with subscripts inside and then pasting by maintaining only the text format (so basically a text that has still the subscript but no cell color or border).

I tried with Macro recording but there is no option in the menu to only paste the text like it is and not the color/borders. I also tried to play a bit with this code but got no results...

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I gave a look for other questions like this but couldn't find any. Thank you in advance!

excel vba

Solution 1:^[1]

Here's an example of how you can do this:

Option Explicit

Sub test()
    CopyText Sheet1.Range("B2"), Sheet1.Range("G2")
    CopyText Sheet1.Range("B4"), Sheet1.Range("G4"), NoBackground:=True, NoBorder:=True
End Sub

Sub CopyText(ByRef src As Range, ByRef dst As Range, _
             Optional ByVal NoBackground As Boolean = False, _
             Optional ByVal NoBorder As Boolean = False)
    src.Copy
    dst.PasteSpecial Paste:=xlPasteAll
    
    With dst
        If NoBackground Then
            .Interior.Pattern = xlNone
            .Interior.TintAndShade = 0
            .Interior.PatternTintAndShade = 0
        End If
        
        If NoBorder Then
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End If
    End With
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	PeterT

'PasteSpecial that removes cell color / Format but keeps the text format

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]