Help with copy and paste macro

[Norm Carrick]

Based on results from the weekly PGA golf tournament, I must select a golfer's name from a field of names on my worksheet and paste it in a column in another part of the sheet. I would like to have a macro that would copy the name I've selected, go to the "Golfer" column, find the next empty cell and paste that name there. Can any one help. My knowledge in creating macros is just enough to get me in trouble and cause serious deficit in my sanity.

 

[John Jairo V]

Hi @Norm Carrick

You can doble click any cell in "M5 currentregion range", and Excel put it into a Golfer column.

Check file with Macro Event. Blessings!

 

[Norm Carrick]

The attached file is an update of my original post which did not supply enough information to get the help I was seeking. The new file shows a number assigned to each member. In a drawing each week, the member receives a new number. Based on the order of finish following the tournament's second round , a golfer is assigned to the member based on his number. I would like to select a golfer, execute the macro and have it paste that name sequentially in the column "Golfer." Then select the next golfer and repeat the process. The incorrect macros have been removed fro the attached file so I need the complete macro.

 

[p45cal]

The new file shows a number assigned to each member.

What number? the file doesn't look any different from your first file.

 

[Norm Carrick]

The list of numbers in the column titled "Week 5."

 

[John Jairo V]

The attached file is an update of my original post which did not supply enough information to get the help I was seeking.

Your new attached file is the same that the another file. The column titled "Week 5" not have any number.

Check file again, and explain with an example in the file what is your purpose with a clear examples. Blessings!

 

[Norm Carrick]

Please note the numbers in Column "Week 5" from 1 to 30. The names listed in the column "Golfer" are in the order they finished after rounds 1 and 2. A copy of this sheet is then sent to the "Entrants" so they will know who their golfer is in our weekly pool. Instead of highlighting a golfer's name, copying it and then pasting it in the column "Golfer", I would like to highlight the name, use a shortcut such as Control p and have the name copied and then pasted in the column "Golfer" following the 1 to 30 order.

 

[p45cal]

Try this in Week5's code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Destn As Range
If Not Intersect(Target, Range("M5").CurrentRegion) Is Nothing Then
  On Error Resume Next
  Set Destn = Range("H4").Offset([match(min(IF(LEN(I5:I33)=0,H5:H33)),H5:H33,0)], 1)
  On Error GoTo 0
  If Not Destn Is Nothing Then
  Cancel = True
  Target.Copy Destn
  End If
End If
End Sub

or with a bit more information:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Destn As Range
If Not Intersect(Target, Range("M5").CurrentRegion) Is Nothing Then
  On Error Resume Next
  Set Destn = Range("H4").Offset([match(min(IF(LEN(I5:I33)=0,H5:H33)),H5:H33,0)], 1)
  On Error GoTo 0
  If Not Destn Is Nothing Then
    vv = Application.Match(Target.Value, Range("I5:I33"), 0)
    If IsError(vv) Then
      Cancel = True
      Target.Copy Destn
    Else
      Set Z = Range("H4").Offset(vv, 1)
      Z.Select
      MsgBox Target.Value & " already in list at selected cell (" & Z.Address(0, 0) & ")."
    End If
  End If
End If
End Sub