VBA Paste Specail

[JeanPyerC]

Hi Fellow Excel Team,

I need some help, I'm trying to figure out a way to paste the value only, however, the closest I've gotten is ActiveSheet.Paste.

When I try to put in ActiveSheet.PasteSpecail xl.PasteValues, I'll get an error message. Can you please help me? Thank you!


---------------------------------------------------------------------------------------------------------------------------------------------

PARTIAL CODE LISTED BELOW

Sub F_E_A_R()
'Forklift Expired Alignment Research

a = Worksheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To a

If Worksheets("DATA").Cells(i, 5).Value = "EXPIRED" Or Worksheets("DATA").Cells(i, 5).Value = "SOON TO EXPIRE" Then

Worksheets("DATA").Cells(i, 5).Offset(0, -4).COPY
Worksheets("REPORT").Activate
B1 = Worksheets("REPORT").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("REPORT").Cells(B1 + 1, 1).Select
ActiveSheet.Paste 'this is where I need help
Worksheets("REPORT").Cells(B1 + 1, 1).Offset(0, 5) = "FORKLIFT"
Worksheets("DATA").Activate
'EMPLOYEE NAME

 

[My Aswer Is This]

You spelled it wrong
Try this:

PasteSpecial xlPasteValues

 

[Alex Blakenburg]

Your code needs a good clean up but in terms of your immediate problem use this.

ActiveCell.PasteSpecial xlPasteValues

 

[Michael M]

What about....

Sub F_E_A_R()
'Forklift Expired Alignment Research
a = Worksheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
b = Worksheets("REPORT").Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To a
    If Worksheets("DATA").Cells(i, 5).Value = "EXPIRED" Or Worksheets("DATA").Cells(i, 5).Value = "SOON TO EXPIRE" Then
        Worksheets("DATA").Cells(i, 5).Offset(0, -4).Copy Worksheets("REPORT").Cells(b + 1, 1)
        With Worksheets("REPORT").Cells(b + 1, 1)
            .Value = .Value
            .Cells(b + 1, 1).Offset(0, 5) = "FORKLIFT"
        End With
    End If
    'EMPLOYEE NAME
Next i
End Sub