Forum Discussion
macro to copy and paste on loop example sent
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
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 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
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 onJuliano-Petrukio
Thanks I was able to make changes to your first code and made it work tyAlan Skelly