CODEWITHSUNDEEP
vbaexcel
Sami
Sami

Reputation: 477

How to only copy values using VBA

I need to copy values only without Formula from sheet to another. The following code does copy but only with Formula. I tried some solutions presented in this site but they give me errors.

For i = 2 To LastRow
    'sheet to copy from
   With Worksheets("Hoist")
        'check column H value before copying
       If .Cells(i, 8).Value >= -90 _
       And CStr(.Cells(i, 9).Value) <> "Approved" _
       And CStr(.Cells(i, 9).Value) <> "" _
       And CStr(.Cells(i, 10).Value) = "" Then
            'copy row to "display" sheet
           .Rows(i).Copy Destination:=Worksheets("display").Range("A" & j)
           j = j + 1
       End If
   End With
Next i

Upvotes: 3

Views: 301

Answers (2)

brettdj
brettdj

Reputation: 55702

Another option is to enter a working column and use AutoFilter to avoid loops

  • insert a column in column A
  • the working column formuka is =AND(I2>-90,AND(J2<>"",J2<>"Approved"),K2="")
  • filter and copy the TRUE rows
  • delete working column A

code

Sub Recut()
Dim ws As Worksheet
Dim rng1 As Range

Set ws = Sheets("Hoist")
ws.AutoFilterMode = False
Set rng1 = Range([h2], Cells(Rows.Count, "H").End(xlUp))

ws.Columns(1).Columns.Insert
rng1.Offset(0, -8).FormulaR1C1 = "=AND(RC[8]>-90,AND(RC[9]<>"""",RC[9]<>""Approved""),RC[10]="""")"

With rng1.Offset(-1, -8).Resize(rng1.Rows.Count + 1, 1).EntireRow
    .AutoFilter Field:=1, Criteria1:="TRUE"
    .Copy Sheets("display").Range("A1")
    Sheets("display").Columns("A").Delete
End With

ws.Columns(1).Delete
ws.AutoFilterMode = False

End Sub

Upvotes: 0

L42
L42

Reputation: 19737

Try changing this line:

.Rows(i).Copy Destination:=Worksheets("display").Range("A" & j)

to this:

.Rows(i).Copy
Worksheets("display").Range("A" & j).PasteSpecial xlPasteValues

This however drops all formatting. To include formatting, you'll need to add another line like:

Worksheets("display").Range("A" & j).PasteSpecial xlPasteFormats

Upvotes: 2

Related Questions