Solved

Paste special failing (VBA)

Posted on 2014-07-31
11
375 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

759 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