VBA Paste data to active cell offset

Question

Hello, Im trying to figure out how to get code to paste to next level down each time ( I would like to copy a range in another workbook and overwrite the previous data ). For example, copy>paste to offset 1,0 first time, then to 2,0 next time, then 3,0 if I run the third time and so on. I attached the code that Im stuck on it, I would appreciate your advices!

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).Select

Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("B4:E4").Copy

    Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("B20").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

End Sub

Answer 1

Hi Robbins, thank you for your reply...still wasn`t copy-paste with your code, it was something similar like mine just instead of "ActiveCell.Offset(1, 0).Select" I should have been used "ActiveCell.Offset(1, 0).Activate"...as I posted my question on another forum I got a reply which solved my issue that I spend yesterday all day long to try to figure it out, here is the code: 

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

Many thanks for sharing your knowledge and your advices!

Answer 2

Hi Robbins, thank you for your reply...still wasn`t copy-paste with your code, it was something similar like mine just instead of "ActiveCell.Offset(1, 0).Select" I should have been used "ActiveCell.Offset(1, 0).Activate"...as I posted my question on another forum I got a reply which solved my issue that I spend yesterday all day long to try to figure it out, here is the code: 

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

Many thanks for sharing your knowledge and your advices!

Answer 3

I am very doubtful about this construction

 Workbooks("Book2.xlsm")

Which I copied from your code

You should probably use

Dim i as long

Dim source as workbook, target as workbook

Set source = Workbooks.Open("Drive:\Path\Book1.xlsm")

Set target = Workbooks.Open("Drive:\Path\Book2.xlsm")

With target.Worksheets("Sheet1").Range("B20")

    For i = 1 To 4

        .Range(.CurrentRegion.Rows(1).Address).Offset(.CurrentRegion.Rows.Count, i - 1).Value = _

        source.Worksheets("Sheet2").Range("A1").Offset(3, i)

    Next i

End With

Answer 4

Hello Robbins, first of all thank you for your reply! I tryed your code but it doesnt update, myabe I didnt expressed myself right... I have 4 columns and thousands of rows, I would like that first row to be copied-pasted in another workbook transposed 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!

Answer 5

Try using

Dim i As Long

With Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("B20")

    For i = 1 To 4

        .Range(.CurrentRegion.Rows(1).Address).Offset(.CurrentRegion.Rows.Count, i - 1).Value = _

        Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("A1").Offset(3, i)

    Next i

End With