Forum Discussion

VersiteRL's avatar
VersiteRL
Copper Contributor
Oct 12, 2022
Solved

VBA copy range to another sheet/file + new rows (from the same range) copy to new row below

Hello,

 

I've tried a number of ways on the Internet, but none of them have worked. Please I would need some advice, how to copy one row to another sheet (preferably to another excel file) and copy the following row from the same row (A39:S39) to a new row under the existing A3 -> A4 - > A5 etc.

 

Please find below my code that made the most sense to me but doesn't work.

 

Thank you very much for any help.

 

Best regards

CODE:

Sub Copy()

unprotect

 

Sheets("Sheet1).Range("A39:S39").Copy Destination:=Sheets("Sheet2").Range("A3").End(xlDown).Offset(1, 0).Row

 

protect

End Sub

excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Oct 12, 2022

    VersiteRL 

    Sub Copy()

Dim l As Long

l = Worksheets("Sheet2").Range("A1").End(xlDown).Row

Worksheets("Sheet1").Range("A39:S39").Copy _
Destination:=Worksheets("Sheet2").Cells(l, 1).Offset(1, 0)

End Sub

    You can try this code. In sheet2 i've added values "x" in cells A1, A2 and A3 in order to make the End(xlDown) command return the expected row number.

6 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 12, 2022

    VersiteRL 

    Sub Copy()

Dim i As Long
Dim j As Long
Dim k As Long

k = Range("A" & Rows.Count).End(xlUp).Row

j = 1

For i = 39 To k

Worksheets("Sheet1").Range(Cells(i, 1), Cells(i, 19)).Copy Destination:=Worksheets("Sheet2").Range("A3").Offset(j, 0)
j = j + 1

Next i

End Sub

    Maybe with this code. In the attached file you can click the button in cell C28 to run the macro.

    • VersiteRL's avatar
      VersiteRL
      Copper Contributor
      Oct 12, 2022
      Hello,

      also find this video - > https://youtu.be/BTYigw1z_RM?t=403 (this is how I'd like the macro to work)
      Tried to use it and macro doesn't work.

      Thank you for any help

      Best regards
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Oct 12, 2022

        VersiteRL 

        Sub Copy()

Dim l As Long

l = Worksheets("Sheet2").Range("A1").End(xlDown).Row

Worksheets("Sheet1").Range("A39:S39").Copy _
Destination:=Worksheets("Sheet2").Cells(l, 1).Offset(1, 0)

End Sub

        You can try this code. In sheet2 i've added values "x" in cells A1, A2 and A3 in order to make the End(xlDown) command return the expected row number.

    • VersiteRL's avatar
      VersiteRL
      Copper Contributor
      Oct 12, 2022

      OliverScheurich 

       

      Hello,

       

      thank you, I tried your macro and it works, but I would like to copy the same row to another new one as below.


      Do you have please any idea how to copy for example from sheet1 (A39:P39) to sheet2 A3 and then click the same button/macro and copy it to sheet2 but to the row below A4 - > after that to A5 and so on?

       

      Thank you in advance for any ideas and help

       

      Best regards

Resources