VBA - Copy entire border style from one cell to another

[gamex]

I have a pivot table that displays some data, and I need to create a macro that creates some cells next to the pivot table. I would like to be able to loop through a column in the pivot table, and copy the entire border style of each cell to another column. I was hoping it would be as simple as this:

ActiveCell.Offset(0, 5).Borders = ActiveCell.Borders

But I guess that would have been wishful thinking.

Is there an easy way to just copy the entire cell's style (border, background color, font style (bold, italic, etc)) to a new cell?

 

[Weaver]

Something like:

ActiveCell.Offset(0, 5).Borders().linestyle = ActiveCell.Borders().linestyle

 

[gamex]

Thanks! Ill give that a try.

 

[Weaver]

you could also try:

Sub test()
    With ActiveCell
        .Copy
        .Offset(, 5).PasteSpecial Paste:=xlPasteFormats
    End With
End Sub

or, if you know the entire range you need to cover beforehand, a more efficient method:

Sub test2()
    With Range("A1:A1000") 'substitute your range here
        .Copy
        .Offset(, 5).PasteSpecial Paste:=xlPasteFormats
    End With
End Sub

Note, this will also copy text formatting.

HTH

 

[gamex]

Thank you, this is exactly what I needed. Thanks!