Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 6
  • 5
1 Solution
 
duncanb7Commented:
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
0
 
Seamus2626Author Commented:
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

0
 
duncanb7Commented:
Could you use two lines instead of one line ?

for example,

.copy
.PasteSpecial  paste:=xlpastevalues

If  so, it will be easier

Duncan
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Thanks
0
 
duncanb7Commented:
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

0
 
Seamus2626Author Commented:
Y15 etc is on the "add delete team members" sheet
0
 
Seamus2626Author Commented:
Il amend and try, thanks!
0
 
duncanb7Commented:
typing error at  on around Y15 ,lastTransferRow , it should be lastTransferRow+1 om my code

Duncan
0
 
duncanb7Commented:
be reminded, your method to use  If  then elseif  else endif
look at Microsoft VBA manual at  http://msdn.microsoft.com/en-us/library/752y8abs.aspx

try this your original idea as following simplify code , but it is strange you use

If  Then

Else
Endif


Duncan
Sub TransferNewData()
Sheets("Change Summary").Select
Set cs = Sheets("Change Summary")
Set adtm = Sheets("Add Delete Team Members")
lastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).Row

With adtm
If .Range("Y15") = 1 Then
.Range("N15:W15").Copy
cs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValues

Else
End If

If .Range("Y16") = 1 Then
.Range("N16:W16").Copy
cs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValues
Else
End If


If .Range("Y17") = 1 Then
.Range("N17:W17").Copy
cs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValues
Else
End If
End With

End Sub

Open in new window

0
 
Seamus2626Author Commented:
Perfect, thanks for your help Duncan!
0
 
duncanb7Commented:
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now