How would you write this ?

[sl1990]

Hi how would you write this as a macro?

Copy X1,X3,X6,X9 and paste them into next available cell in the A column of a sheet called "result" in the same workbook.


Thanks in advance

Cheers!

 

[dfenton21]

Hi There,

This macro will do what you describe

Sub copycells()

    sResult = Range("x1") & " " & Range("x3") & " " & Range("x6") & " " & Range("x9")

    Sheets("result").Range("A" & Sheets("result").Range("A65536").End(xlUp).Row) = sResult
    

End Sub

Please note your post said that you want X1, X3, X6, X9 to be pasted "into next available cell". Therefore, I'm assuming that the four values are to appear in one cell.

Please let me know if you have any further queries this with

 

[dave3009]

Something like...

Sub foo()
Dim lst As Long
Dim i As Long
Dim item As Variant

lst = Sheets("result").Range("A" & Rows.Count).End(xlUp).Row
i = 1
For Each item In Array("X1", "X3", "X6", "X9")
    Sheets("result").Range("A" & lst + i) = Range(item)
    i = i + 1
Next item
End Sub

 

[Nalani]

Another one just a little shorter using Areas:

Sub test()
    Dim myArea As Range
    Dim LR As Long
    LR = Sheets("result").Range("A" & Rows.Count).End(xlUp).Row
    For Each myArea In Range("X1, X3, X6, X9").Areas
        Sheets("result").Range("A" & LR + 1) = myArea
        LR = LR + 1
    Next
End Sub

 

[sl1990]

Thanks so much Dfenton21, Dave3009, Nalani.

I found Dave3009's works best for me in this instance, Is there any way to make it copy the cells color across to the "result" sheet.?

cheers

 

[dave3009]

Try

Sub foo()
Dim lst As Long
Dim i As Long
Dim item As Variant

lst = Sheets("result").Range("A" & Rows.Count).End(xlUp).Row
i = 1
For Each item In Array("X1", "X3", "X6", "X9")
    With Sheets("result").Range("A" & lst + i) 
       .Value = Range(item).Value
       .Interior.Colorindex = Range(item).Interior.ColorIndex
    End With
    i = i + 1
Next item
End Sub