CODEWITHSUNDEEP
excelexcel-formulavba
jumbli
jumbli

Reputation: 35

Pastespecial, paste only values from formual

I have 2 sheets, in one sheet I am looking for a specifik text in a column, if that exists then it should copy all the rows with the specific text and paste them in another sheet. That is working for me, but the problem is that when I want to pastespecial, only paste the values and not the formulas I isn't working.

Here is the code, any idea what to do?

With Sheets(1)
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i).Value = "Orange" Then .Rows(i).Copy
        Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
        Paste:=xlPasteValues

    Next i

End With

Upvotes: 0

Views: 78

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

While the answer of Rory should fit perfectly your problem, you could speed it up by a big amount using a variable for the ranges to copy...

Dim rng As Range
With Sheets(1)
  LR = .Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To LR
    If .Range("A" & i).Value = "Orange" Then
      If rng Is Nothing Then
        Set rng = .Rows(i)
      Else
        Set rng = Union(rng, .Rows(i))
      End If
    End If
  Next
  rng.EntireRow.Copy
  Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With

Written by phone. May contain errors.

Upvotes: 0

Rory
Rory

Reputation: 34045

You have a logic error in the code, which I missed in my comment. You need the PasteSpecial inside the If block:

With Sheets(1)
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i).Value = "Orange" Then
          .Rows(i).Copy
           Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
        Paste:=xlPasteValues
        End If

    Next i

End With

Upvotes: 2

Related Questions