Forum Discussion
macro to copy and paste on loop example sent
read note in example file. sheet 1
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
5 Replies
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 SubThank 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 thisJuliano-Petrukio
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
