CODEWITHSUNDEEP
vbaexcel
mgri
mgri

Reputation: 267

Maintaining destination data format when copying style in VBA?

I am trying to copy the style from a specific column (formatted as Text starting from its second row) to another column (formatted as Date starting from its second row). Both columns stores values.

I am able to copy-and-paste the style to the destination column:

.Columns("A").Copy '# "A" is the starting column
.Columns(dest_col).PasteSpecial Paste:=xlPasteFormats '# dest_col is the destination column

but this code also formats it as a Text column, while I want to keep its original formatting (i.e. Date starting from the second row).

Is there any option I can use to prevent this behavior?

Upvotes: 3

Views: 252

Answers (1)

Vityata
Vityata

Reputation: 43595

You may try to take only the values of the specific parameters, which you are interested in (E.g., style, Interior Color, Font Color etc.)

The following works only when the whole column has the same format, as far as I did not to loop through every cell:

Option Explicit

Sub TestMe()

    Dim colFrom     As Long
    Dim colTo       As Long

    colFrom = 1
    colTo = 5
    CopyFullFontAndInterior colFrom, colTo

End Sub

Sub CopyFullFontAndInterior(colFrom As Long, colTo As Long, Optional wsN As Long = 1)

    Dim copyFrom    As Range
    Dim copyTo      As Range

    With Worksheets(1)
        Set copyFrom = .Range(.Cells(1, colFrom), .Cells(2 ^ 20, colFrom))
        Set copyTo = .Range(.Cells(1, colTo), .Cells(2 ^ 20, colTo))
    End With

    copyTo.Style = copyFrom.Style
    If copyFrom.Interior.Color > 0 Then copyTo.Interior.Color = copyFrom.Interior.Color
    If copyFrom.Font.Color > 0 Then copyTo.Font.Color = copyFrom.Font.Color

End Sub

A possible workaround is to save the format of a given cell of the column in a variable and to use it after the .PasteSpecial:

Sub TestMe()

    Dim saveOurFormat As String

    saveOurFormat = Columns(5).Cells(2).NumberFormat
    Columns("A").Copy
    Columns(5).PasteSpecial Paste:=xlPasteFormats
    Columns(5).NumberFormat = saveOurFormat
    Application.CutCopyMode = False

End Sub

Upvotes: 1

Related Questions