asked on

Paste special failing (VBA)

If Range("Y15") = 1 Then
Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1) PasteSpecial Paste:=xlPasteValues

This line of code has incorrect syntax, can someone suggest new syntax or a new line of code to transfer the range from one sheet to another using paste special

Thanks

duncanb7

Did you do .select and .activate the sheet ? Just reminder

Try this,

Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)

Open in new window

Duncan

Seamus2626

ASKER

Hi Duncan,

Below is my sub, with your line added, im getting the error message

"Unable to get the pastespecial property of the range class"

Thanks

Sub TransferNewData()

LastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).Row

If Range("Y15") = 1 Then
Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)

Else
End If

If Range("Y16") = 1 Then
Sheets("Add Delete Team Members").Range("N16:W16").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)
Else
End If


If Range("Y17") = 1 Then
Sheets("Add Delete Team Members").Range("N17:W17").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)
Else
End If


End Sub

Open in new window

duncanb7

Could you use two lines instead of one line ?

for example,

.copy
.PasteSpecial paste:=xlpastevalues

If so, it will be easier

Duncan

Seamus2626

ASKER

Ya, i dont mind, if you could plug it in and it works, id be very happy!!

Thanks

duncanb7

Y15,Y16,Y17 , is it on Change Summary sheet,right ?

Try this

Duncan

Sub TransferNewData()
Sheets("Change Summary").Select
lastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).Row


If Sheets("Change Summary").Range("Y15") = 1 Then
Sheets("Add Delete Team Members").Range("N15:W15").Copy
Sheets("Change Summary").Range("A" & lastTransferRow).PasteSpecial Paste:=xlPasteValues

Else
End If

If Sheets("Change Summary").Range("Y16") = 1 Then
Sheets("Add Delete Team Members").Range("N16:W16").Copy
Sheets("Change Summary").Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValues
Else
End If


If Sheets("Change Summary").Range("Y17") = 1 Then
Sheets("Add Delete Team Members").Range("N17:W17").Copy
Sheets("Change Summary").Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValues
Else
End If
'Sheets("Change Summary").Range("Y" & lastTransferRow).Select
End Sub

Open in new window

Seamus2626

ASKER

Y15 etc is on the "add delete team members" sheet

Seamus2626

ASKER

Il amend and try, thanks!

duncanb7

typing error at on around Y15 ,lastTransferRow , it should be lastTransferRow+1 om my code

Duncan

ASKER CERTIFIED SOLUTION

duncanb7

membership