CODEWITHSUNDEEP
excelvba
Big_Papa_B
Big_Papa_B

Reputation: 139

Copying Formatting in Excel

I am facing an issue with a small piece of VBA I have been using.

I have values in Column A, I want only values in bold to remain into Column A.

The Code I am using is:

Sub CopyBoldText()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrBelow = 0
Dim c As Range
For Each c In Intersect(Columns("B"), ActiveSheet.UsedRange)
If c.Font.Bold Then c.Offset(, -1).Value = c.Value
Next c
End Sub

My issue is that the values in Column A are numbers with proceeding zero's when the values are copied and inserted in Column A these leading values are dropped.

I have tried varying the CopyOrigin switch between the following :

xlFormatFromLeftOrBelow = 0
xlFormatFromLeftOrBelow = 1
xlFormatFromRightOrBelow = 0
xlFormatFromRightOrBelow = 1

But each time the values still appear without the leading Zeroes.

Upvotes: 0

Views: 383

Answers (3)

Mrig
Mrig

Reputation: 11702

Try this

Sub CopyBoldText()
    Columns("A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrAbove
    Columns("B").Copy
    Columns("A").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    Dim c As Range
    For Each c In Intersect(Columns("B"), ActiveSheet.UsedRange)
        If c.Font.Bold Then c.Offset(, -1).Value = c.Value
    Next c
End Sub

Upvotes: 0

Tony Wilson
Tony Wilson

Reputation: 44

Try adding the line

Columns("A:A").NumberFormat = "@"

after the Selection.Insert. This will make the format of the whole column text which is the only way that you will not lose the leading zeroes on numbers.

Upvotes: 1

FunThomas
FunThomas

Reputation: 29171

If this is a formatting issue, why not copy the format with PasteSpecial for the whole column.

Columns("B").Copy
Columns("A").PasteSpecial Paste:=xlPasteFormats

Upvotes: 0

Related Questions