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

Hello,

thank you very much, it works great. The previous rows are being overwritten - your macro works great with only values but in my sheet I copy cells with formula. Is it possible to copy only values? So it wont overwrite?

 

+


Is it possible to copy the range to another close worksheet? (For example C:\Users\example\Desktop\excel[example.xlsx]Sheet2 and copy it to A3 (for example)

Thank you very much for your time and help.

Resources