PDA

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



shekhu
10-12-2016, 05:22 AM
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
10-12-2016, 06:06 AM
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
10-18-2016, 03:41 AM
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