CODEWITHSUNDEEP
excelvba
FuriousD
FuriousD

Reputation: 119

VBA Copy Destination with formats not just values

I have code which goes to the end of a row then adds text which is in a specified cell of another sheet. This works well however now I want to modify this to copy the text and the formatting that in place.

I thought the change If IsEmpty(cell) = True Then cell.Value = Sheets("Text").Range("A2").Copy Destination: Exit For would work but this gives me an error.

I also tried setting the cell I wanted to copy as a range and referring to that however this also gave me the value only and not the formatting.

I also tried If IsEmpty(cell) = True Then cell.Value = Sheets("Text").Range("A2").Copy: Exit For and the result was entered as True.

Is there a simple way I should be doing this?

Sub AddText()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    'Set ws = Sheets("Selection")
    For Each cell In ws.Columns(1).Cells
               If IsEmpty(cell) = True Then cell.Value = Sheets("Text").Range("A2").Value: Exit For
    Next cell
End Sub

Upvotes: 0

Views: 2231

Answers (1)

FunThomas
FunThomas

Reputation: 29171

You are mixing up 2 different things

a) Assigning a value

cell.Value = Sheets("Text").Range("A2").value 
cell = Sheets("Text").Range("A2")    ' Basically the same, you will see this very often

This will copy the content (and only the content) of one cell to another.

b) Copy & Paste

Sheets("Text").Range("A2").Copy cell
Sheets("Text").Range("A2").Copy Destination:=cell  ' Synonym

This will copy the whole Information of the source cell ("A2") into the destination. It is basically the same as Press Ctrl+C and Ctrl+V in Excel

c) Just to be complete: You can also use PasteSpecial to copy only certain parts (formatting, formula, value, number format...). This needs to be done with 2 lines:

Sheets("Text").Range("A2").Copy
cell.PasteSpecial xlPasteAll

There are lots of options for that, (see https://learn.microsoft.com/de-de/office/vba/api/excel.range.pastespecial) and you know that command from PasteSpecial in Excel.

So what you probably want is

 For Each cell In ws.Columns(1).Cells
     If IsEmpty(cell) Then 
         Sheets("Text").Range("A2").Copy cell
         Exit For
    End If
Next cell

Upvotes: 1

Related Questions