Using VBA to Paste Values and Fonts Only

[CV355]

Hello all,

I am working on an application that requires me to paste a large range of data. I only want to paste the values and text format, leaving source borders and background colors as is. I have tried the various xlPaste______ options but none seem to handle this task.

Am I missing something?

One workaround that I'd prefer to avoid would be pasting all and using VBA to reformat the cells, but it seems excessive.

 

[sheetspread]

It's not excessive if you paste values, then loop through and sync the fonts (2 steps). Or also change values while looping.

 

[mumps]

You could reformat the range that you are pasting like this:

Sub Test()
    Columns("A").Font.Name = "Algerian"
End Sub

Replace the column with the pasted range and the font name with the name of the source range font.