Solved

How to get Paste Special Values to work in VBA with Excel

Posted on 2011-03-24
13
636 Views
Last Modified: 2012-05-11
I have the attached code which worked fine until I changed the paste statement.  The copied data has formulas but I only want the values.  Now I get Run-Time Error 1004 with Application defined or object-defined error.  This is all in VBA module in Excel.
Public Sub DataSourceRatings(strWbkName As String)
Dim wks             As Worksheet
Dim MyName          As Name
Dim strMyName       As String
Dim intText         As Integer
Dim intLength       As Integer
Dim strFinalName    As String
Dim strRangeName    As String
Dim strLastColumn   As String

'RATINGS DATA SECTION
With Workbooks(strWbkName)
    .Activate
    .Worksheets("DataSource").Activate
    .Worksheets("DataSource").Range("A1") = "RATING"
    .Worksheets("DataSource").Range("B1") = "ClientRatingsDetail"

For Each wks In Workbooks(strWbkName).Worksheets
    If wks.Name Like "RATING*" Then
     strLastColumn = LastColumn(wks.Name)
     
    .Worksheets("DataSource").Activate
    
'Need to copy at least one row from a RATING worksheet for the headers on the DataSource worksheet
 If Worksheets("DataSource").Range("A2").Value = 0 Then
    wks.Activate
    wks.Range("A5:" & strLastColumn & "5").Copy
    .Worksheets("DataSource").Activate
            Range("A2").Select
            ActiveSheet.PasteSpecial Paste:=xlPasteValues  'Gives application-defined or object-defined error here!
End If
        strMyName = CStr(wks.Name)
        intText = InStr(wks.Name, "-")
        intLength = Len(wks.Name)
        strFinalName = Right(strMyName, intLength - intText)
        strRangeName = "RAT" & strFinalName
        Debug.Print "Range name: " & strRangeName
Range(strRangeName).Copy Worksheets("DataSource").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    End If
Next

End With
    Call DeleteBlankEntireRows(strWbkName)
End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 8
  • 5
13 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35208529
Is .Worksheets("DataSource") protected, hidden or otherwise special in any way?

Thomas
0
 

Author Comment

by:ssmith94015
ID: 35208619
Nope
0
 

Author Comment

by:ssmith94015
ID: 35208629
If I just leave the phrase as paste, it works.  It was when I was trying to change it to PasteSpecial that it starts to throw an error.  However, one of the columns that is pasted is calculated - I don't want the calculation, I want the value.  This process loops through about 50 worksheets and pastes all their data onto one master worksheet.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35208717
how about

.Worksheets("DataSource").Activate
.Worksheets("DataSource").Range("A2").Select
.ActiveSheet.PasteSpecial

Open in new window


If that's not working, could you post a template file so I can try and reproduce the error?
0
 

Author Comment

by:ssmith94015
ID: 35208795
Still pastes the formula, it needs the Paste:=xlPasteValues, but I do not know how to tell it this as what I have does not work.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35208828
I forgot that part, do you get the error with:

.Worksheets("DataSource").Activate
.Worksheets("DataSource").Range("A2").Select
.ActiveSheet.PasteSpecial Paste:=xlPasteValues

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:ssmith94015
ID: 35208891
Still get the same error at the PasteSpeical statement.
0
 

Author Comment

by:ssmith94015
ID: 35208945
Ok, this is to my error.  The section that I have posted is actually OK.  It is only pasting the headers.  The area I actually need to change is toward the bottom, where the statement

Range(strRangeName).Copy Worksheets("DataSource").Cells(Rows.Count, 1).End(xlUp).Offset(1)

is located.  This is the actual data that is being copy/pasted and thus the statement that needs to paste speical the values.  I am so sorry, I am getting into a hurry as time is running out.
0
 

Author Comment

by:ssmith94015
ID: 35208956
BUt I think I am still going to have the same problem.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35209047
try

Range(strRangeName).Copy 

with Worksheets("DataSource")
.activate
.Cells(Rows.Count, 1).End(xlUp).Offset(1).select
.pastespecial paste:=xlpastevalues
end with

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 35209088
I don't think there is a solution.  I tried that and am still getting the same error.
0
 

Author Closing Comment

by:ssmith94015
ID: 35209146
nutsch, you were on the right track.  I made one change, added the term Selection and it worked.

        With Worksheets("DataSource")
        .Activate
        .Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

Boy, that was painful!  Thank you again.

Sandra
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35209220
Glad to help.

Thomas
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now