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

Question

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

User · Accepted Answer

To copy formatting   Range  F10   Select Selection Copy Range  I10 J10   Select   note that we select the whole merged cell Selection PasteSpecial Paste  xlPasteFormats   copying the formatting will break the merged cells  so you can use this to put the cell back together  Range  I10 J10   Select Selection Merge   To copy a cell value  without copying anything else  and not using copy paste   you can address the cells directly  Range  I10   Value   Range  F10   Value   other properties  font  color  etc   can also be copied by addressing the range object properties directly in the same way

User · Answer

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 · Answer

Sub CopyValueWithFormatting       Sheet1 Range  A1   Copy     With Sheet2 Range  B1            PasteSpecial xlPasteFormats          PasteSpecial xlPasteValues     End With End Sub

User · Answer

Using Excel 2010   Try  Selection PasteSpecial Paste  xlPasteAllUsingSourceTheme  Operation  xlNone  SkipBlanks  False  Transpose  False

User · Answer

I prefer to avoid using select       With sheets  sheetname   range  I10               PasteSpecial Paste  xlPasteValues                      Operation  xlNone                      SkipBlanks  False                      Transpose  False            PasteSpecial Paste  xlPasteFormats                      Operation  xlNone                      SkipBlanks  False                      Transpose  False            font color   sheets  sheetname   range  F10   font color       End With       sheets  sheetname   range  I10 J10   merge

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

Examples related to excel

Examples related to vba

Examples related to formatting

Examples related to copy

Examples related to cell