A more efficient way of doing this

[P5X]

OK, basically I am copying and pasting 3 cells from one sheet into another, I can't copy all the cells at once due to how they are arranged on the sheet, so I was wondering if there would be an easier way of doing this than copying the cell, selecting the sheet and then pasting the cell (3 times)

[C7].Copy
Sheets("Component_list").Select
[A40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select
[C9].Copy
Sheets("Component_list").Select
[B40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select
[C11].Copy
Sheets("Component_list").Select
[C40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select

 

[Barrie Davidson]

On 2002-02-17 10:26, P5X wrote:
OK, basically I am copying and pasting 3 cells from one sheet into another, I can't copy all the cells at once due to how they are arranged on the sheet, so I was wondering if there would be an easier way of doing this than copying the cell, selecting the sheet and then pasting the cell (3 times)

[C7].Copy
Sheets("Component_list").Select
[A40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select
[C9].Copy
Sheets("Component_list").Select
[B40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select
[C11].Copy
Sheets("Component_list").Select
[C40].End(xlUp).Offset(1, 0).PasteSpecial
Sheets("Add_components").Select

This will work for you (if I understand your requirements):

Range("C7,C9,C11").Copy
Range("A40").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False

 

[P5X]

Thanks, that was just what I wanted