PasteSpecial how to

[owen4512]

Hi everyone,

The below bit of code will copy then paste dates that are less than or equal to 6 months from today's date. The data on "sheet1" is formatted differently to where the data will be pasted on "sheet2". How can i make it so it will only paste the values and not copying the format too?

Sub extractDataBasedOnDate()

Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("A1").Select


For i = 2 To lastrow


    mydate = Sheet1.Cells(i, 2)
    If mydate <= DateValue(Date) - 6 Then
    erow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 1), Cells(i, 3)).Copy Destination:=Sheets("sheet2").Cells(erow, 3)
    End If
    
Next i
MsgBox "Complete"


End Sub

Thanks

 

[Fluff]

Try

For i = 2 To LastRow


    mydate = Sheet1.Cells(i, 2)
    If mydate <= DateValue(Date) - 6 Then
    erow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Sheets("sheet2").Cells(erow, 3).Resize(, 3).Value = Range(Cells(i, 1), Cells(i, 3)).Value
    End If
    
Next i

 

[owen4512]

Thank you for the fast response.

That's done it. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback