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

dzrdw0 - Aug 28, 2010 at 11:19 PM - Latest reply:  dzrdw0
- Sep 8, 2010 at 11:27 PM

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


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)
End With
End Sub

Hopy someone can help me out. Thanks.

See more 

3 replies

mysterry60 270 Posts Thursday September 2, 2010Registration date October 4, 2010 Last seen - Sep 7, 2010 at 05:15 AM
Thank you
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()
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,
Thank you

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