SOLVED

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

Copper Contributor

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

6 Replies

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

@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

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
best response confirmed by VersiteRL (Copper Contributor)
Solution

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


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.

@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

 

 

1 best response

Accepted Solutions
best response confirmed by VersiteRL (Copper Contributor)
Solution

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

View solution in original post