How copy vba cells

Question

I need to copy cell E12 on the same sheet in the same sheet without taking the formatting of the source cell, formatting the destination with font size and color different from the source but I don't understand how to give the command.

Range("E12").Copy Range("P" & (Rows.Count)).End(xlUp).Offset(1, 0).Interior.Color = vbRed

Hereby some interesting reading material: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Dominique, Jun 28 '21 at 08:48

score 1 · Answer 1 · edited Jun 28 '21 at 08:44

In order to copy a cell without taking the formatting you could also just write the value of your desired cell. Excel will then just take the "Value" within a cell and write it to a different one without including the formatting.

Something like this:

Sub Test1()
Dim Ws As Worksheet

Set Ws = ActiveSheet

Ws.Cells(7, 2).Value = Ws.Cells(6, 2).Value
End Sub

Here i refer to the cells themselves instead of the range, but it can also be done using a range.

Sub Test1()
Dim Ws As Worksheet

Set Ws = ActiveSheet

Ws.Range("B7").Value = Ws.Range("B6").Value
End Sub

After filling the cell with just the value you can change the formatting however you like by using a With statement.

Sub Test1()
Dim Ws As Worksheet

Set Ws = ActiveSheet

Ws.Range("B7").Value = Ws.Range("B6").Value

With Ws.Range("B7")
    .Font.Name = "Arial"    'Name of the font you want to use
    .Font.Color = vbYellow  'Color of the font you want to use
    .Font.Size = 22         'Size of your font
    .Interior.Color = vbRed 'Color of the cell
    .Font.Bold = True       'Making the text bold True/False
End With

End Sub

Wizhi · Accepted Answer · 2021-06-28T08:30:31.930

This is one approach.

The .PasteSpecial have several option to paste values:

No. Options of Paste Type
1   xlPasteAll
2   xlPasteAllExceptBorders
3   xlPasteAllMergingConditionalFormats
4   xlPasteAllUsingSourceTheme
5   xlPasteColumnWidths
6   xlPasteComments
7   xlPasteFormats
8   xlPasteFormulas
9   xlPasteFormulasAndNumberFormats
10  xlPasteValidation
11  xlPasteValues
12  xlPasteValuesAndNumberFormats

I would wrap it in With ws .... End With I assume that the pasted range should be colored with red. Otherwise just add the offset part.

Sub test()

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

With ws
   .Range("E12").Copy
   .Range("P" & (Rows.Count)).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("P" & (Rows.Count)).End(xlUp).Interior.Color = vbRed
End With

End Sub

How copy vba cells

2 Answers2