Copying the cell value preserving the formatting from one cell to another in excel using VBA

user_1582311 0
Viewed 133431

In excel, I am trying to copy text from one cell to another cell in another sheet. The source cell contains formatted text (bold,underlined,different colors). But when I copy the text using VBA to the other cell, the formatting is lost.

I know it is because excel is copying only the text value. Is there a way we can read the HTML text (rather than plain text) from a cell?

I have googled this and did not get any answers. I know that if we use copy and paste methods, we can copy the formatting. E.g.

Range("F10").Select
Selection.Copy
Range("I10").Select
ActiveSheet.Paste

But I want to do it without a copy and paste since my destination is a merged cell and not identically sized as my source cell. Is there an option available in excel VBA to do this?

EDIT: I was able to solve it with the following code.

Range("I11").Value = Range("I10").Value
For i = 1 To Range("I10").Characters.Count
    Range("I11").Characters(i, 1).Font.Bold = Range("I10").Characters(i, 1).Font.Bold
    Range("I11").Characters(i, 1).Font.Color = Range("I10").Characters(i, 1).Font.Color
    Range("I11").Characters(i, 1).Font.Italic = Range("I10").Characters(i, 1).Font.Italic
    Range("I11").Characters(i, 1).Font.Underline = Range("I10").Characters(i, 1).Font.Underline
    Range("I11").Characters(i, 1).Font.FontStyle = Range("I10").Characters(i, 1).Font.FontStyle
Next i
cell copy formatting vba excel
5 Answers

Copying the Bold Text From one sheet to another sheet in excel By using VBScript 'Create instance Object

Set oXL = CreateObject("Excel.application")
oXL.Visible = True

Set oWB = oXL.Workbooks.Open("FilePath.xlsx")
Set oSheet = oWB.Worksheets("Sheet1")         'Source Sheet in workbook
Set oDestSheet = oWB.Worksheets("Sheet2")       'Destination sheet in workbook

r = oSheet.usedrange.rows.Count
c = oSheet.usedrange.columns.Count

For i = 1 To r
    For j = 1 To c
        If oSheet.Cells(i,j).font.Bold = True Then

            oSheet.cells(i,j).copy
            oDestSheet.Cells(i,j).pastespecial
        End If
    Next
Next

oWB.Close
oXL.Quit
user_90026570
Related