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...
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.

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 

         

        I don't know how can I add a file to the thread. (I'm getting an error that the file is not supported and I'm using yours.) - send you your edited file with formulas to the dm's

         

        Thank you in advance

         

         

  • 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