VBA How to copy the content of a cell without .Select

Question

I am writing a method that can take the Target and paste the cell exactly to another cell. The cell is a shipping label with some fancy formatting. Is there a way I can do it?

Originally I have this:

Worksheets("Label").Range("A1").Value = Worksheets("Get Address").Range("A28").Value

It worked for the plain text. However I lost the styling I created, they are different because after the first line, the style is different:

I also tried to use Macro Recorder and I got a solution using .Select, and I read this question not to use it whenever possible. What can I do?

' Created by the Macro Recorder
Range("A28:A33").Select
Range("A33").Activate
Selection.Copy
Sheets("Label").Select
Range("A1").Select
ActiveSheet.Paste

Your code can be re-writtenn in a single line `Range("A33").Copy Sheets("Label").Range("A1")` — Santosh, May 22 '13 at 18:19

score 24 · Accepted Answer · answered May 22 '13 at 18:19

24

Worksheets("Get Address").Range("A33").Copy _
       Destination := Worksheets("Label").Range("A1")

answered May 22 '13 at 18:19

Tim Williams

154,628
8
97
125

score 7 · Answer 2 · answered Jan 02 '15 at 19:15

7

to copy and paste values only then use following

Worksheets("Label").Range("A1").value = _
   Worksheets("Get Address").Range("A33").value

this statement will not use clip board

answered Jan 02 '15 at 19:15

sai baba

71
1
1

score 0 · Answer 3 · answered Jun 19 '20 at 08:24

You can loop thru each cell of the range to copy and set each cell's value, comment, etc in the target destination. Here's an example.

Sub CopySpecial(p_RangeFrom As String, p_OffsetRow As Integer, p_OffsetColumn As Integer)

    Dim l_Row As Integer
    Dim l_Column As Integer
    Dim thisCell As Range
    Dim l_TargetCell As Range

    l_Row = Range(p_RangeFrom).Row
    l_Column = Range(p_RangeFrom).Column

    For Each thisCell In Range(p_RangeFrom)

        Set l_TargetCell = Range(Cells(thisCell.Row + p_OffsetRow, thisCell.Column + p_OffsetColumn).Address)

        ' Copy the text
        l_TargetCell.Value = thisCell.Value

        ' Copy the comment only if we have a comment to copy
        If Not thisCell.Comment Is Nothing Then

            ' Delete any existing comment in the target cell, if any.
            ' If you don't to this the .AddComment Method below will fail.
            If Not l_TargetCell.Comment Is Nothing Then l_TargetCell.Comment.Delete
            Call l_TargetCell.AddComment(thisCell.Comment.Text)

        End If

        ' Add more items to copy here, such as color, etc.

    Next

End Sub

Sub TestCall()
    Call CopySpecial("A1:B2", 3, 3)
End Sub

VBA How to copy the content of a cell without .Select

3 Answers3

Linked