Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Paste special failing (VBA)

Posted on 2014-07-31
11
Medium Priority
?
382 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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