Forum Discussion
VBA copy range to another sheet/file + new rows (from the same range) copy to new row below
- Oct 12, 2022
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.
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.
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
- OliverScheurichOct 12, 2022Gold Contributor
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.
- VersiteRLOct 12, 2022Copper Contributor
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. - VersiteRLOct 12, 2022Copper Contributor
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