• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

with excel vba how can I transfer the text in one cell to another with losing all the formatting

I have highly formatted cells which I want to transfer to other cells progrommaticly with vba
how do I do this without losing formatting?
0
Dov_B
Asked:
Dov_B
  • 2
4 Solutions
 
Martin LissRetired ProgrammerCommented:
To copy both values and formatting do something like this.

    Selection.Copy
    Range("A3").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
0
 
dsackerContract ERP Admin/ConsultantCommented:
This is an example. You can change out the "Range" for "Cells", if you are looping through columns or rows, and use the same methods.
Public Sub TestThis()
    ' Have a specially formatted value in D5, and move it to F5
    Range("D5").Select
    Selection.Copy
    Range("F5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ' If you need to clear the formatting in your source, do this.
    Range("D5").Clear
End Sub

Open in new window

0
 
Shanan212Commented:
    Range("H14").Select
    Selection.Copy
    Range("J14").Select
    ActiveSheet.Paste
application.cutcopymode=false

Open in new window


Considering you want to copy from H14 to J14
0
 
Martin LissRetired ProgrammerCommented:
And in my example if you just want to copy the formatting do just the first of the two PasteSpecials.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now