Excel VBA - Copy & Paste Help

[ststern45]

Hi everyone,

Need help creating VBA code to copy and past a cell range to another cell.

For example,

Cell A1 = 001
Cell A2 = 101
Cell A3 = 365

I would like to copy, paste and transpose cell range A1:A3 to cell range B1 keeping the same format.

For example, B1 would look like the following: 001,101,365 (place commas between the sets)

Thank you in advance!!

 

[Michael M]

Maybe this
It does ALL cells in col "A"
If you don't want ALL cells, change the lr to the number of cells required

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr
    If Range("B1").Value = "" Then
        Range("B1").Value = Range("A" & r).Value
        Else:
        Range("B1").Value = Range("B1").Value & "," & Range("A" & r).Value
    End If
Next r
End Sub

 

[Tetra201]

Try

Sub Test()
    Range("B1").NumberFormat = "@"
    Range("B1") = Join(WorksheetFunction.Transpose(Range("A1:A3").Text), ",")
End Sub

 

[ststern45]

Thanks for all your help. I will use these examples.