VBA copy value and format

Question

How can I change the following code to copy not only the value, but also the font style, for example. bold or not bold. Thanks

Private Sub CommandButton1_Click() Dim i As Integer Dim a As Integer a = 15 For i = 11 To 32 If Worksheets(1).Cells(i, 3) <> "" Then Worksheets(2).Cells(a, 15) = Worksheets(1).Cells(i, 3).Value Worksheets(2).Cells(a, 17) = Worksheets(1).Cells(i, 5).Value Worksheets(2).Cells(a, 18) = Worksheets(1).Cells(i, 6).Value Worksheets(2).Cells(a, 19) = Worksheets(1).Cells(i, 7).Value Worksheets(2).Cells(a, 20) = Worksheets(1).Cells(i, 8).Value Worksheets(2).Cells(a, 21) = Worksheets(1).Cells(i, 9).Value a = a + 1 End If Next i 

Answer 1

Instead of setting the value directly, you can try using copy / paste, so instead:

 Worksheets(2).Cells(a, 15) = Worksheets(1).Cells(i, 3).Value 

Try the following:

 Worksheets(1).Cells(i, 3).Copy Worksheets(2).Cells(a, 15).PasteSpecial Paste:=xlPasteFormats Worksheets(2).Cells(a, 15).PasteSpecial Paste:=xlPasteValues 

To simply install the font in bold, you can save the existing destination and add this:

 If Worksheets(1).Cells(i, 3).Font.Bold = True Then Worksheets(2).Cells(a, 15).Font.Bold = True End If 

Answer 2

Following jpw, it would be nice to encapsulate its solution in a small routine to save on having a lot of lines of code:

 Private Sub CommandButton1_Click() Dim i As Integer Dim a As Integer a = 15 For i = 11 To 32 If Worksheets(1).Cells(i, 3) <> "" Then call copValuesAndFormat(i,3,a,15) call copValuesAndFormat(i,5,a,17) call copValuesAndFormat(i,6,a,18) call copValuesAndFormat(i,7,a,19) call copValuesAndFormat(i,8,a,20) call copValuesAndFormat(i,9,a,21) a = a + 1 End If Next i end sub sub copValuesAndFormat(x1 as integer, y1 as integer, x2 as integer, y2 as integer) Worksheets(1).Cells(x1, y1).Copy Worksheets(2).Cells(x2, y2).PasteSpecial Paste:=xlPasteFormats Worksheets(2).Cells(x2, y2).PasteSpecial Paste:=xlPasteValues end sub 

(I do not have Excel at the current location, so please excuse the errors if they are not tested)

Answer 3

This page from the Microsoft Excel VBA documentation helped me: https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype

This gives a bunch of options to customize the insertion method. For example, you can xlPasteAll (perhaps what you are looking for), or xlPasteAllUsingSourceTheme , or even xlPasteAllExceptBorders .

Answer 4

Found it on OzGrid thanks to Aaron Blood - everything is simple and works.

 Code: Cells(1, 3).Copy Cells(1, 1) Cells(1, 1).Value = Cells(1, 3).Value 

However, I suspect that you simply provided us with a simplified example to ask a question. If you just want to copy formats from one range to another, it looks like this ...

 Code: Cells(1, 3).Copy Cells(1, 1).PasteSpecial (xlPasteFormats) Application.CutCopyMode = False