Macro to copy selected cells in a row and paste into new sheet at specified locations

[shekhu]

Hi All,

Can someone help me create a Macro to copy selected cells in a row (e.g. A TO H) and create new sheet (by copying from a Template Sheet) and paste the copied cells "A to H" at specified locations. Please see attached .xls file for reference.

Also, is it possible to copy contents of a "cell comments" and paste in a new sheet?

Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(2)


Selected Cell("A") Copy to Cells(C4)
Selected Cell("B") Copy to Cells(A10)
Selected Cell("C") Copy to Cells(A7)
Selected Cell("D") Copy to Cells(B7)
Selected Cell("E") Copy to Cells(A4)
Selected Cell("F") Copy to Cells(B4)
Selected Cell("G") Copy to Cells(B10)
Selected Cell("H") Copy to Cells(C7)
Comments in Cell("B") Copy to Cells(B16)

Please Suggest.

[mana]

ption Explicit

Sub test()
    Dim r As Range
    
    Set r = Selection.EntireRow       
    Sheets("Template").Copy After:=Sheets(2)

    With ActiveSheet
        .Range("c4").Value = r.Range("a1").Value
        .Range("a10").Value = r.Range("b1").Value
        .Range("a7").Value = r.Range("c1").Value

     
        On Error Resume Next
        .Range("b16").Value = r.Range("b1").Comment.Text
        On Error GoTo 0
    End With
    
End Sub

[bglumac]

Did the selection worked?
I'm trying to make similar code to work and my problem is that on every code I tried i Can't get the range from selection.
Basically what I need is to reset the values of combo boxes upon selection via button.
So I want to select a part of the table and reset it.
What I use is:

Private Sub Selection_Click()
    Selection.Value = "-"
End Sub

It's linked to the button, but it's not working (it's on active worksheet).
Do I need to dim or set another range ?

For now, I select it and the Run-time error '13' ... Type mismatch is triggered, yellowing

Selection.Value = "-"

upn debug

THX