Copy non-contiguous cells to another sheet [Solved/Closed]

Report
-
 dzrdw0 -
Hello,

I have the code below that only works for copying cells B2,B3,C3,E2,E4 from active sheet to the same identical cells on sheet2. Cells, B2,B3,C3,E2,E4, are where user inputing info.The tasks I'd like to accomplish is that, I want the cells B2,B3,C3,E2,E4 being copied to sheet2 in such orders shown on a row, and copy to the next empty row after each time user inputting new info on cells, cells, B2,B3,C3,E2,E4.

Sheet1 copy to Sheet2
B2 ------> A2
B3 ------> B2
C3 ------> C2
E2 ------> D2
E4 ------> E2

Code:

Sub test()
Dim I As Long
With ActiveSheet.Range("B2,B3,C3,E2,E4")
For I = 1 To .Areas.Count
.Areas(i).Copy Sheets("Sheet2").Range(.Areas(i).Address)
Next
End With
End Sub

Hopy someone can help me out. Thanks.




Related:

3 replies


Dear Sir,

Please get through the following helpful tips in order to get solving the problem.

https://ccm.net/faq/4938-how-to-compare-two-excel-sheets-and-combine-unique-data

https://ccm.net/faq/5113-excel-how-to-transfer-worksheet-to-another-excel-file

https://ccm.net/faq/6033-excel-copy-data-from-one-sheet-to-another

Thanks in advance.
Hi dzrdw0,

I know there are shorter ways of writing a code for this query (I just don't know how) but since you only have 5 cells to copy/paste try this:

Sub test()
Sheets("sheet1").Range("B2").Copy
If Sheets("sheet2").Range("A2").Value = "" Then Sheets("sheet2").Range("A2").PasteSpecial Else Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
End Sub

The only assumption I made is that the cells below the destination cells of sheet2 are empty.
And as you can see this is only the code for one value.

Let me know how this works for you.

Best regards,
Trowa
Trowa,

Thanks for the response. Yes. this is exactly what I'm looking for. Thanks again.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!