Paste Special

[olympiac]

How can I modify the following code to paste only the value?

Selection.Copy _
Destination:=Workbooks("name.xls").Sheets("Result").Cells(1, 1).End(xlDown).Offset(1, 0)

Thanks

 

[VoG]

Try

Selection.Copy
Workbooks("name.xls").Sheets("Result").Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

 

[olympiac]

Genial. Many thanks

 

[olympiac]

This code works:
Workbooks("name.xls").Sheets("Result").Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues


What's wrong with this one?
Workbooks("name.xls").Sheets("Result").Cells(2, 1).PasteSpecial Paste:=xlPasteValues

 

[VoG]

That should work. Do you get an error or what?

 

[olympiac]

Here is the code:
If the first row in Sheet Result is empty then I want to paste the value if not I want to jump to the second row and so on.
The row is highlighted in red in my code for
Selection.Copy _
Workbooks("AccessReview Automated tool V2.xls").Sheets("Result").Cells(2, 1).PasteSpecial Paste:=xlPasteValues


CODE:

If Worksheets("Result").Cells(1, 1) = "" Then


Sheets("New").Select
Range("D8:H8").Select

Selection.Copy _
Workbooks("name.xls").Sheets("Result").Cells(1, 1)

ElseIf Worksheets("Result").Cells(2, 1) = "" Then

Sheets("New").Select
Range("D8:H8").Select

Selection.Copy _
Workbooks("name.xls").Sheets("Result").Cells(2, 1).PasteSpecial Paste:=xlPasteValues

Else

Sheets("New").Select
Range("D8:H8").Select


'Selection.Copy _
'Destination:=Workbooks("name.xls").Sheets("Result").Cells(1, 1).End(xlDown).Offset(1, 0)
Selection.Copy
Workbooks("name.xls").Sheets("Result").Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

 

[olympiac]

Apologies.
the error was due to the "_" in "Selection.Copy_"
I removed it and it works

Thanks

 

[njimack]

That should be on 2 separate lines of code, without the underscore at the end of the first line.

Also, you very rarely need to select sheets/cells to perform actions. For example:

Sheets("New").Select
Range("D8:H8").Select

Selection.Copy _
Workbooks("name.xls").Sheets("Result").Cells(1, 1)

could be rewritten as:

Sheets("New").Range("D8:H8").Copy Workbooks("name.xls").Sheets("Result").Cells(1, 1)

 

[VoG]

Try

Sheets("New").Range("D8:H8").Copy
Workbooks("name.xls").Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

 

[olympiac]

Good tip.

Cheers