?
Solved

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

Posted on 2011-03-24
13
Medium Priority
?
653 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
[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
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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 …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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