Macro copies formulas instead of values.

JAMiller · #1 (**permalink**) 09-16-11, 16:29

I have the following macro and it works but it copies the formula instead of the values.

I need help in editing the macro--I have researched this problem and the solution is PasteSpecial but I do not know how to incorporate into this code??

Here is the macro:

Sub RowCopy()
Dim rngFind As Range

With Worksheets("Summary").UsedRange
Set rngFind = .Find("Totals", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
rngFind.EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
End Sub

Colin Legg · #2 (**permalink**) 09-17-11, 10:14

Welcome to the forum.

Try this:

Code:

Sub RowCopy()
    
    Dim rngFind As Range
    
    Set rngFind = Worksheets("Summary").UsedRange.Find( _
                            What:="Totals", _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=True)
    
    If Not rngFind Is Nothing Then
        Worksheets("Sheet3").Range("A1").EntireRow.Value = rngFind.EntireRow.Value
    End If
    
End Sub

JAMiller · #3 (**permalink**) 09-17-11, 10:23

Hi Colin

That worked great. Thank you.

Internet Services

Web Development

Digital Marketing

Consumer Tech