Solved

Paste special failing (VBA)

Posted on 2014-07-31
11
378 Views
Last Modified: 2014-07-31
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
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40231206
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
 

Author Comment

by:Seamus2626
ID: 40231210
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40231212
Could you use two lines instead of one line ?

for example,

.copy
.PasteSpecial  paste:=xlpastevalues

If  so, it will be easier

Duncan
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Seamus2626
ID: 40231213
Ya, i dont mind, if you could plug it in and it works, id be very happy!!

Thanks
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40231223
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
 

Author Comment

by:Seamus2626
ID: 40231232
Y15 etc is on the "add delete team members" sheet
0
 

Author Comment

by:Seamus2626
ID: 40231233
Il amend and try, thanks!
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40231260
typing error at  on around Y15 ,lastTransferRow , it should be lastTransferRow+1 om my code

Duncan
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 500 total points
ID: 40231266
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
 

Author Closing Comment

by:Seamus2626
ID: 40231340
Perfect, thanks for your help Duncan!
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40231343
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question