Macro to copy/paste item from list

[roheba]

I have a list of names in Range A20:A55. Starting from Cell A20, I want to invoke a macro to copy current cell on the list to location "C3" then return to list and move down 1row to next item on the list. Then calculate and stop. Will repeat the macro from each item on the list, then calc and stop.

 

[roheba]

Update. Seems like such a simple macro... but I just can't get it right. To clarify, I want to invoke macro from top of list, at Cell A20. Want to copy contents of Call A20 to Cell C3. Then return to Cell A20 and move down to Cell A21. Then stop the macro. Then invoke the macro again from A21, copy contents to C3, move down to Cell A22, stop. Etc, etc. Seems so simple. I'm pulling my hair. Mucho thanks in advance.

 

[My Aswer Is This]

I know of no macro which can be run to start and stop and start and stop and start and stop

Why do you thinks you need to do things like this?

 

[My Aswer Is This]

What are we copying?
And what do you plan to do when the macro is stopped?

 

[roheba]

Ok. I guess I was unclear. I thought "invoke" meant "to begin." What I need is really as simple as I explained: Copy contents from here to there, go down one row from here, and end the macro. After that, a complex iterative calculation process automatically runs. Then, when I'm ready, I can run the macro again from its new position (down one row from here). Hope that helps... and sincere thanks.

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Peter_SSs]

If you are going to work your way right through the list in one session of excel, then try this

Sub Copy_Values_v1()
  Static rCurr As Range
  
  If rCurr Is Nothing Then Set rCurr = Range("A19")
  Set rCurr = rCurr.Offset(1)
  If rCurr.Row < 56 Then
    rCurr.Copy Destination:=Range("C3")
  Else
    MsgBox "Finished"
  End If
End Sub

If you might work part way through the list one day then come back on another day and wan to continue from where you left off, then we would need to store some information somewhere - I've used cell Z1.

Sub Copy_Values_v2()
  Dim rStore As Range
  
  Set rStore = Range("Z1")
  rStore.Value = rStore.Value + 1
  If rStore.Value < 20 Then rStore.Value = 20
  If rStore.Value < 56 Then
    Range("A" & rStore.Value).Copy Destination:=Range("C3")
  Else
    MsgBox "Finished"
  End If
End Sub

 

[roheba]

Peter,

Thanks for the solutions. Both work well and I am using the latter version.

Problem solved, but…

I’m really curious to see if I might learn some VBA coding by asking you to critique (and perhaps tweak) my own effort.

Sub MyTryMacro1()

Range("A20").Select
ActiveWorkbook.Names.Add Name:="Spot1", RefersToR1C1:="=Analysis!R20C1"
ActiveWorkbook.Names("Spot1").Comment = ""
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Application.Goto Reference:="Spot1"
ActiveWorkbook.Names("Spot1").Delete
Application.CutCopyMode = False
Range("A21").Select

End Sub

As you can see, I took a different approach. I want to be able to place the cursor on any cell in the list of names, then by running the macro, do the copying and go to the next lower cell. Clearly, it doesn’t work because of the various absolute cell references. Is there a way to make this approach work such that the cell references would be determined by whatever the current cell is when the macro is invoked? Furthermore, it seems rather clumsy to jump from cell to cell to put the information into Cell C3. I’d guess you can suggest a more elegant way to accomplish this.

Again, much thanks for your attention and assist.

 

[Peter_SSs]

Is there a way to make this approach work such that the cell references would be determined by whatever the current cell is when the macro is invoked?

In general I don't much like working with selecting cells/ranges and in many codes, selecting slows the code considerably. However, try

Sub Test()
  If Intersect(ActiveCell, Range("A20:A55")) Is Nothing Then Range("A20").Select
  Range("C3").Value = ActiveCell.Value
  ActiveCell.Offset(1).Select
End Sub

BTW, when posting code, please use Code tags to preserve any indentation.