• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

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

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
Sandra Smith
Asked:
Sandra Smith
  • 8
  • 5
1 Solution
 
nutschCommented:
Is .Worksheets("DataSource") protected, hidden or otherwise special in any way?

Thomas
0
 
Sandra SmithRetiredAuthor Commented:
Nope
0
 
Sandra SmithRetiredAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
nutschCommented:
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
 
Sandra SmithRetiredAuthor Commented:
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
 
nutschCommented:
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
 
Sandra SmithRetiredAuthor Commented:
Still get the same error at the PasteSpeical statement.
0
 
Sandra SmithRetiredAuthor Commented:
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
 
Sandra SmithRetiredAuthor Commented:
BUt I think I am still going to have the same problem.
0
 
nutschCommented:
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
 
Sandra SmithRetiredAuthor Commented:
I don't think there is a solution.  I tried that and am still getting the same error.
0
 
Sandra SmithRetiredAuthor Commented:
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
 
nutschCommented:
Glad to help.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now