excelvba

Copy paste cells from button row


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

Solution

  • 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