Copy non-contiguous cells to another sheet

dzrdw0 - Aug 28, 2010 at 11:19 PM
 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.

3 responses

Blocked Profile
Sep 7, 2010 at 05:15 AM
Dear Sir,

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

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()
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,

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