Copy and paste only specific cells from worksheet

[ccsher77]

Hi All,


I pretty new to the world of VBA so please forgive my possible simplistic questions.


Ok we basically what I want to do is using a command button copy and paste only certain cells from worksheet 1 to the next empty row on worksheet 2.


I have worked out how to do this for complete rows but what I can't figure out is ho to do it if the data is on seperate rows/columsn.


For example:


The data i wish to copy is on worksheet1 in the following cells:

A1,B2,C1,D2


I want select only those cells and then paste it to the next empty row on sheet2


so A1 would be pasted to A1 B2 to A2 C1 to C1 and D2 to D1


I hope this makes sense can anyone help?


Thanks in advance

 

[NARAYANK991]

Hi ,


You can use a button to run a macro to do this , provided the procedure is the same always ; can you say what will happen if the data is in cells A2 , B1 , C1 and D2 on worksheet1 ? Will these cells go to worksheet2 in the cells A5 , B5 , C5 and D5 , assuming that row 5 is the next empty row in sheet2 ?


Narayan

 

[ccsher77]

Hi NARAYANK991,


Thanks for your reply.


You are correc the procedure will alwys be the same, the cells to be copied will be fixed and I am trying to get them to paste into the next unused row as per your comment.


As I mentioned I can work out how to do this is the cells to be copied were A1 thru to D1

But I can make it do it for cells not on the same row/column, I am thinking perhaps I need to use an Array command but as of yet I have had no luck!


Any help would be much appreciated

 

[SirJB7]

Hi, ccsher77!

Would you mind posting the code you use for complete rows or for cells in same row? Otherwise cnsider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[NARAYANK991]

Hi ,


Can you try this ?

[pre]

Public Sub Copy_Paste_Cells()
Dim copy_cells As Range
Set copy_cells = Range("A1,B2,C1,D2")

With ThisWorkbook.Worksheets("Sheet2")
Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
For i = 1 To copy_cells.Areas.Count
.Cells(Lastrow, i) = copy_cells.Areas(i).Value
Next
End With
End Sub

[/pre]
Narayan