VBA copy-paste offset to another workbook

问题:

I would need your advice if you could help me because my VBA knowledge is very poor. I have 4 columns and thousands of rows, I would like that first row to be copied-pasted transposed in another workbook by hitting a command button, after that the second hit of button to overwrite the previous copy-paste and so on (in the second workbook I have some formulas which are gonna apply to the new values and I have to check the results). Many thanks in advance!

Here is the code that I have done it to copy-paste from one workbook to the other one (the code is moving to the next row but it doesn't copy-paste the values on the other workbook, in the other workbook are the same first values, so here its my dilemma):

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).Activate

Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("B4:E4").Copy
Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("B20").PasteSpecial Transpose:=True

Application.CutCopyMode = False

End Sub

回答1:

Please try this

Private Sub CommandButton1_Click()
ActiveCell.Offset(1, 0).Activate
Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("A" & ActiveCell.Row & ":E" & ActiveCell.Row).Copy
Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("B20").PasteSpecial Transpose:=True
End sub