macro to copy and paste on loop example sent

[Alan Skelly]

read note in example file. sheet 1

[#2 · Accepted solution]

@Alan Skelly 

Hello, I hope I can help you

Well, to be honest I didn't understand the main purpose of transfer all values from 1 cell to to another via code.

But I can presume you are looking for something that can take piece of piece and then do this transferring. I prepare the following loop so you can use in your workbook.

Remember that what this code is doing is breaking the names separated by comma and transferring one by one to another cell, just like you wrote. Please advise me if is something different from this.

Sub TranferValuesbyLoop()
    Dim ListOfNames() As String
    ListOfNames = Split(Worksheets("sheet1").Range("D4").Value, ", ")
    
    Dim item As Variant, Destination As Range
    Set Destination = Worksheets("sheet2").Range("E4")
        
    For Each item In ListOfNames
        If Len(Destination) > 0 Then
            Destination.Value = Destination.Value & "," & item
        Else
            Destination.Value = item
        End If
    Next item
    
End Sub

[#3]

Thank you for response works good, but my fault in explanation. I don't want to have sheet 2 look like sheet 1 in end. I want first name pasted then next pasted over that. Jim Smith pasted in then Ann waash over that. not jim smith, then ann waash added.  jim smith pasted in cell I'll run macro associated with jim smith. then loop around paste in ann waash over jim smith and I'll run macro associated with ann waash. until last name is done. Also the paste has to be paste special.values. Thanks again for looking at this@Juliano-Petrukio 

[#4]

@Alan Skelly 

Looks like you want to invert the order of the names 

Original: Jim Smith, Annn wash, Hammerton, Polhollow, Undermam

Result: Undermam, Polhollow, Hammerton, Annn wash, Jim Smith

Regarding Paste Values only you don't need to worry because the values are stored in an variable called ListOfNames. Only the values are stored there. As looks like you want to invert the order, the function ReverseArray does that and then each value of the reversed list is "paste" in the cell E4 of the sheet2.

Option Explicit

Sub TranferValuesbyLoop()
    Dim ListOfNames As Variant
    ListOfNames = Split(Worksheets("sheet1").Range("D4").Value, ", ")
    ListOfNames = ReverseArray(ListOfNames)
    Dim item As Variant, Destination As Range
    Set Destination = Worksheets("sheet2").Range("E4")
        
    For Each item In ListOfNames
        If Len(Destination) > 0 Then
            Destination.Value = Destination.Value & ", " & item
        Else
            Destination.Value = item
        End If
    Next item
    
End Sub

Function ReverseArray(arr As Variant) As Variant
    Dim val As Variant

    With CreateObject("System.Collections.ArrayList")
        For Each val In arr
            .Add val
        Next val
        .Reverse
        ReverseArray = .Toarray
    End With
End Function

[#5]

I don't know where you got that from my explanation.  Trying to think how I can explain.  Put Jim smith  in sheet 2 . that will trigger a macro I have for jim smith to run. jim smith macro runs. then put ann waaash into sheet2 (jim smith goes away). triggering a macro for ann waash to run. Then put hammerton in sheet2 (ann waash goes away). triggering a macro for hammerton to run. same until last name in string is done.  I am putting code to run when the cell value of sheet 2 changes  I do not want a range of names in sheet 2 like in sheet 1. I want first name brought over then the second name erasing the first name so on and so on@Juliano-Petrukio 

[#6]

Thanks I was able to make changes to your first code and made it work ty@Alan Skelly