Solved

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

Posted on 2011-03-24
13
644 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

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.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

831 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