• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • 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
ssmith94015
Asked:
ssmith94015
  • 8
  • 5
1 Solution
 
nutschCommented:
Is .Worksheets("DataSource") protected, hidden or otherwise special in any way?

Thomas
0
 
ssmith94015Author Commented:
Nope
0
 
ssmith94015Author 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
ssmith94015Author 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
 
ssmith94015Author Commented:
Still get the same error at the PasteSpeical statement.
0
 
ssmith94015Author 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
 
ssmith94015Author 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
 
ssmith94015Author Commented:
I don't think there is a solution.  I tried that and am still getting the same error.
0
 
ssmith94015Author 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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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