Copy non-contiguous cells to another sheetSolved/Closed

Question

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.




Configuration: Windows XP / Internet Explorer 7.0

mysterry60 · Answer

Dear Sir, 

Please get through the following helpful tips in order to get solving the problem. 

https://ccm.net/apps-sites/software/4025-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.

Trowa · Answer

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

dzrdw0 · Answer

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

Copy non-contiguous cells to another sheet

3 responses

Similar discussions

Subscribe To Our Newsletter!