I wrote code that after a button click in Sheet2 would copy the first 10 cells from the same row as button and should paste them in Sheet1 cell T2.
When I execute the code, in Sheet1 the T2+10 cells are selected but no data pasted.
Sub CopyRowToSheet1()
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim sourceRow As Long
Set wsSource = ActiveSheet
Set wsTarget = ThisWorkbook.Sheets("Sheet1")
sourceRow = ActiveCell.Row
wsSource.Range(wsSource.Cells(sourceRow, "A"), wsSource.Cells(sourceRow, "J")).Copy
wsTarget.Range("T2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
Using Application.Caller
and assigning values directly:
Sub CopyRowToSheet1()
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim sourceRow As Long, shp As Object
Set wsSource = ActiveSheet
Set wsTarget = ThisWorkbook.Sheets("Sheet1")
Set shp = wsSource.Shapes(Application.Caller) 'get the clicked button shape object
With shp.TopLeftCell.EntireRow.Range("A1:J1") 'range is *relative* to row
'assign values directly
wsTarget.Range("T2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub